Sum Total # Of Cells That Meet Several Criteria

A

ashstudly

Supervisor Sun
Adam S Mower 11:00-16:00
Adam S Mower 11:00-19:30
Adam S Mower Off
Adam S Mower 8:30-13:30
Candice S Carter Off
Candice S Carter Off
Candice S Carter 8:00-16:30
Chris S Uigaese 8:00-13:00
Chris S Uigaese Off
Chris S Uigaese 12:00-20:00
Chris S Uigaese 8:00-16:30
Chris S Uigaese 8:00-16:30


I'm trying to make a formula that autmatically counts the number of
"off"'s that appear in the chart above that are listed with a certain
person... I just can't seem to come up with the function. Above is an
example. The names are in a separate column. Any help?
 
G

Govind

Hi,

Assuming that the data is in the range A1 to B100, and the person to
whom you want the count is listed in cell C1, use this formula

=SUMPRODUCT(($A$1:$A$100=C1)*(B1:B100="Off"))

Regards

Govind.
 
A

ashstudly

I can't believe how easy that was... I've tryingthat forever! Thank you
so much! Here is another one...This time summing up the total for each
person.

Supervisor Total
Adam S Mower 5.65
Adam S Mower 6.04
Adam S Mower 8.09
Adam S Mower 7.34
Candice S Carter 1.24
Candice S Carter
Candice S Carter 1.98
Chris S Uigaese 2.43
Chris S Uigaese 9.78
Chris S Uigaese 4.33
Chris S Uigaese
Chris S Uigaese 3.78
 
G

Govind

Hi,

Use

=SUMPRODUCT(($A$1:$A$100=C1)*(B1:B100))

where A1 to A100 has the names of the supervisor and B1:B100 has the
hours. C1 is the cell where you have typed out the name of the person
for whom you need the total.

Regards

Govind.
 
B

Biff

Hi!

Try this:

Use a cell to hold the criteria:

C1 = Adam S Mower

Names in column A, amounts in column B.

=SUMIF(A1:A100,C1,B1:B100)

Biff
 
Top