CountIF AND Help

[KuraiChikara]

Hello, I am trying to perform a Countif or a Countifs AND function and I'm having issues.
I have success with IF AND functions but can't seem to get COUNTIF AND functions to work.

Working Shift Examples:
Employees can have 7 combinations of days off formatted:
[TABLE="width: 80"]
<tbody>[TR]
[TD]F--MTWR[/TD]
[/TR]
[TR]
[TD]--SMTWR[/TD]
[/TR]
[TR]
[TD]FY--TWR[/TD]
[/TR]
[TR]
[TD]FYS--WR[/TD]
[/TR]
[TR]
[TD]FYSM--R[/TD]
[/TR]
[TR]
[TD]FYSMT--[/TD]
[/TR]
[TR]
[TD]-YSMTW-[/TD]
[/TR]
</tbody>[/TABLE]



In the example below I would want to figure out "who comes in on Monday at 6:30 AM?"

There are 9 week types and 2 of those types have people that don't come in Monday at all (Sun/Mon off or Mon/Tues off in asterisk) which would mean 7 agents come in at 6:30 AM on Monday but I can't set that up in a formula.

I've tried the following:
=COUNTIF(AND(B1=C1,B1=C2,B1=C3,B1=C4,B1=C5,B1=C6,B1=C7)B1:B10,C1))
(and simply nest the formula for the 6 other day of the week types. If the formula worked shown above, I was looking at the Sat/Sun off people and there are 4 of them, if I nest it I can include all the other week types that exclude agents who don't come in on Monday which totals 7).

The issue I have with the COUNTIFAND function is after the AND function parenthesis:
C7)B1:B10,C1)) where the parenthesis ends on C7 and the B1 begins I need a COMMA to go into the range portion of the COUNTIF, but when I add the comma, it jumps immediately to criteria.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Week[/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]F--MTWR[/TD]
[TD]F--MTWR[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]--SMTWR[/TD]
[TD]--SMTWR[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]FY--TWR[/TD]
[TD]FY--TWR***[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]FYS--WR[/TD]
[TD]FYS--WR***[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]FYSM--R[/TD]
[TD]FYSM--R[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]FYSMT--[/TD]
[TD]FYSMT--[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]-YSMTW-[/TD]
[TD]-YSMTW-[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]F--MTWR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]F--MTWR[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Why not just:

=COUNTIF(B2:B10,"*M*")

 

[KuraiChikara]

You're a genius buddy, Excel has a million ways to skin a cat and I simply didnt know that method. I actually fixed my issue and created a more complex formula but I had to include information like day off abbreviations. Sa, Su, Mo, Tu, etc.
The data below, like the grid above with days off instead of week and criteria. I used this formula to calculate the same thing.

=COUNTIFS($B$37:$B$43,"SA",$C$37:$C$43,"SU",$A$37:$A$43,A37)+COUNTIFS($B$37:$B$43,"SU",$C$37:$C$43,"MO",$A$37:$A$43,A37)+COUNTIFS($B$37:$B$43,"MO",$C$37:$C$43,"TU",$A$37:$A$43,A37)+COUNTIFS($B$37:$B$43,"TU",$C$37:$C$43,"WE",$A$37:$A$43,A37)+COUNTIFS($B$37:$B$43,"WE",$C$37:$C$43,"TH",$A$37:$A$43,A37)+COUNTIFS($B$37:$B$43,"TH",$C$37:$C$43,"FR",$A$37:$A$43,A37)+COUNTIFS($B$37:$B$43,"FR",$C$37:$C$43,"SA",$A$37:$A$43,A37)

[TABLE="width: 202"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD]Day Off[/TD]
[TD]Day Off[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]Su[/TD]
[TD]Mo[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[/TR]
[TR]
[TD]6:30 AM[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Well, I'm glad you sussed it out.

A couple of thoughts. First, if the days off are always together, as they seem to be, then you don't need to check column C, because it already depends on column B, which you're checking. Second, there is a shortcut way to add a bunch of COUNTIFS like you're doing. Say you want to count everyone who comes in on Monday, then this should work:

=SUM(COUNTIFS($B$37:$B$43,{"Sa","Tu","We","Th","Fr"},$A$37:$A$43,A37))

I left out Su and Mo from the array since those two groups have Mondays in them.

Anyway, good luck!