COUNTIFS Help

[KuraiChikara]

Hello,
I am trying to perform a task similar to what a COUNTIFS would do but I can't seem to get it.
Columns A-F are used.

I have agents, a work week (IE F--MTWR), start time and Criteria. # of agents or column D is where the formula is.

Example:
If I want to see how many people have a 6:30 AM shift, that's 7 people below.
But if I want to find the number of agents who work at 6:30 AM and have a F--MTWR schedule (which should be 3) I can't calculate that.

The formula I've tried is the following and it calculates zero:
=COUNTIFS(B2:C8,E2,B2:C8,F2)

Agent	Week	Time	# of Agents	Start Criteria	Week Criteria
John	F--MTWR	6:30 AM	7	6:30 AM	F--MTWR
Jack	F--MTWR	6:30 AM	0	6:45 AM	--SMTWR
Bill	F--MTWR	6:30 AM		7:00 AM	FY--TWR
Sam	FY--TWR	6:30 AM		7:30 AM	-YSMTW-
Susan	FYSMT--	6:30 AM		8:00 AM	FYSMT--
Karen	FYSM--R	6:30 AM		8:30 AM	FYSM--R
Mary	FYS--WR	6:30 AM		9:00 AM	FYS--WR
				9:30 AM
				10:00 AM
				10:30 AM
				11:00 AM
				11:30 AM
				12:00 PM
				12:15 PM
				12:30 PM
				1:00 PM
				1:30 PM
				2:00 PM
				3:00 PM
				3:30 PM
				4:00 PM
				5:00 PM
				10:00 PM
				10:30 PM

<tbody>
</tbody>

 

[jtakw]

Hi,

I think this is what you mean:

Book1
	A	B	C	D	E	F
1	Agent	Week	Time	# of Agents	Start Criteria	Week Criteria
2	John	F--MTWR	6:30 AM	3	6:30 AM	F--MTWR
3	Jack	F--MTWR	6:30 AM		6:45 AM	--SMTWR
4	Bill	F--MTWR	6:30 AM		7:00 AM	FY--TWR
5	Sam	FY--TWR	6:30 AM		7:30 AM	-YSMTW-
6	Susan	FYSMT--	6:30 AM		8:00 AM	FYSMT--
7	Karen	FYSM--R	6:30 AM		8:30 AM	FYSM--R
8	Mary	FYS--WR	6:30 AM		9:00 AM	FYS--WR
Sheet90

Cell Formulas
Range	Formula
D2	=COUNTIFS(C$2:C$8,E2,B$2:B$8,F2)

 

[MUKESHY12390]

you get error even if you use correct formula???

than you need to consider below points:-

1)Check Time format , is it in Time format>
2)Check whether any extra space in Week value.
try manually do filter check each record whether it's matching to your criteria OR NOT.

 

[KuraiChikara]

That's exactly what I mean. Thank you. I was grouping the range to include both criteria. You represented each criteria in the COUNTIFS function separately (look at the Time column C2:C8 now look at the Week column B2:B8) instead of all at once. Thank you.

 

[jtakw]

You're welcome, glad it works for you.