KuraiChikara
Board Regular
- Joined
- Nov 16, 2016
- Messages
- 111
- Office Version
- 2013
- Platform
- Windows
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:
<tbody>
</tbody>
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.
<tbody>
</tbody>
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:
F--MTWR |
--SMTWR |
FY--TWR |
FYS--WR |
FYSM--R |
FYSMT-- |
-YSMTW- |
<tbody>
</tbody>
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.
Time | Week | Criteria |
6:30 AM | F--MTWR | F--MTWR |
6:30 AM | --SMTWR | --SMTWR |
6:30 AM | FY--TWR | FY--TWR*** |
6:30 AM | FYS--WR | FYS--WR*** |
6:30 AM | FYSM--R | FYSM--R |
6:30 AM | FYSMT-- | FYSMT-- |
6:30 AM | -YSMTW- | -YSMTW- |
6:30 AM | F--MTWR | |
6:30 AM | F--MTWR |
<tbody>
</tbody>