Sum Total # Of Cells That Meet Several Criteria

[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?

[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.

ashstudly wrote:
> 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?
>
>

[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

[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.

ashstudly wrote:
> 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
>
>

[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

"ashstudly" <ashstudly.222osm_1137994801.1597@excelforum-nospam.com> wrote
in message news:ashstudly.222osm_1137994801.1597@excelforum-nospam.com...
>
> 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
>
>
> --
> ashstudly
> ------------------------------------------------------------------------
> ashstudly's Profile:
> http://www.excelforum.com/member.php...o&userid=30720
> View this thread: http://www.excelforum.com/showthread...hreadid=503907
>