In article ,
blackstar wrote:

now that that's fixed.. is it possible using that formula to have it readjust
itself if rows are added or removed. ex a2:a14 exists but if i add 5 people i
want to formula to include a2:a21. same if i were to remove 2 people so i
would only want it to look @ cell a2:12. i know this is easy to do manually
but i need this setup so the dumbest person can use it easily.

Since you have only one condition, you can use SUMIF instead which will
allow you to reference a whole column...

=SUMIF(A:A,"Spring",B:B)

Also, SUMIF is more efficient than SUMPRODUCT. Alternatively, you can
define a dynamic range...

Insert Name Define

Name: CondRange

Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(REPT ("z",255),Sheet1!$A$2:$
A$65536))

Click Add

Name: RangeToSum

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(REPT ("z",255),Sheet1!$A$2:$
A$65536))

Click Ok

Change the 'Names' and sheet references accordingly.

Then, use the following formula...

=SUMIF(CondRange,"Spring",RangeToSum)

Hope this helps