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)

[TABLE="width: 594"]
<tbody>[TR]
[TD]Agent[/TD]
[TD]Week[/TD]
[TD]Time[/TD]
[TD]# of Agents[/TD]
[TD]Start Criteria[/TD]
[TD]Week Criteria[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]F--MTWR[/TD]
[TD]6:30 AM[/TD]
[TD] 7[/TD]
[TD]6:30 AM[/TD]
[TD]F--MTWR[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]F--MTWR[/TD]
[TD]6:30 AM[/TD]
[TD] 0[/TD]
[TD]6:45 AM[/TD]
[TD]--SMTWR[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]F--MTWR[/TD]
[TD]6:30 AM[/TD]
[TD][/TD]
[TD]7:00 AM[/TD]
[TD]FY--TWR[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]FY--TWR[/TD]
[TD]6:30 AM[/TD]
[TD][/TD]
[TD]7:30 AM[/TD]
[TD]-YSMTW-[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]FYSMT--[/TD]
[TD]6:30 AM[/TD]
[TD][/TD]
[TD]8:00 AM[/TD]
[TD]FYSMT--[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]FYSM--R[/TD]
[TD]6:30 AM[/TD]
[TD][/TD]
[TD]8:30 AM[/TD]
[TD]FYSM--R[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]FYS--WR[/TD]
[TD]6:30 AM[/TD]
[TD][/TD]
[TD]9:00 AM[/TD]
[TD]FYS--WR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9:30 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:30 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:30 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:15 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:30 PM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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