Countifs

[KuraiChikara]

Hello, I'm trying to perform a countifs with multiple criteria but part of the multiple criteria is in the same column and I can't get the countifs to calculate properly.

I am trying to ask, "How many people work on this day of the week at this time?"

One start time and one day of the week I can calculate just fine:
Example:
=COUNTIFS($B$2:$B$5,"MO",$A$2:$A$5,A2)
This formula comes out to 4.

Start	Week
6:30 AM	Mo
6:30 AM	Mo
6:30 AM	Mo
6:30 AM	Mo

<tbody>
</tbody>

However, if I add an additional day of the week for a criteria, it gives me a zero.
=COUNTIFS($B$2:$B$5,"MO",$B$2:$B$5,"TU",$A$2:$A$5,A2)

I don't get why I'm getting zero. Should be 4.

Start	Week
6:30 AM	Mo
6:30 AM	Mo
6:30 AM	Tu
6:30 AM	Tu

<tbody>
</tbody>

 

[Weazel]

Re: COUNTIFS Help

you would need to configure the countifs to handle an OR criteria....

=SUM(COUNTIFS(B2:B5,{"MO","TU"},A2:A5,A2))

this would give you 2 for MO and 2 for TU and the sum would add them together.

 

[KuraiChikara]

Re: COUNTIFS Help

Thank you. I also just figured this one out after I typed my question

=COUNTIFS($B$2:$B$5,"MO",$A$2:$A$5,A2)+COUNTIFS($B$2:$B$5,"TU",$A$2:$A$5,A2)

But your formula is less bulky.

Thank you!