CountIf Function (I think) NEED HELP

[Kahuna8372]

I need to generate some details from information that's exported to Excel. Here is the Export from the host program to Excel:

B	C	D	E	F	G	H	I	J	K	L
Name	Status	From	To	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Employee 1	Available	07:00	17:00	False	True	True	True	True	True	False
Employee 2	Available	05:00	20:00	True	True	True	True	True	True	True
Employee 3 etc...	Available	04:00	04:00	True	False	False	True	True	False	False

<tbody>
</tbody>

From this information I need to determine how many employees are available to work during certain day parts each day, my results table is set up as so:

Daypart	Sun	Mon	Tue	Wed	Thu	Fri	Sat
5am-11am	2
11am-2pm	3
etc

<tbody>
</tbody>

I have been working with the following CountIf formula, but don't have correct results:

COUNTIFS(D2:D4,">=05:00", E2:E4,"<11:00")+COUNTIF(F2:F4,"TRUE")

I'm stuck. Any help would be greatly appreciated.

 

[wigi]

Hello,

You need to use Countifs (plural)

Assuming:
- your data is in cells B1:L4
- your headers of the 2nd table are in A6:I6, with headers: Daypart Start / Daypart End / Sun / Mon / Tue / Wed / Thu / Fri / Sat
- your data for the 2nd table is in A7:B8

this will work in cell C7:

=COUNTIFS($C$2:$C$4,"Available",INDEX($F$2:$L$4,,MATCH(C$6,$F$1:$L$1,0)),TRUE,$D$2:$D$4,"<="&$A7,$E$2:$E$4,">="&$B7)

Then copy/paste the formula for the other rows and weekdays.

 

[Kahuna8372]

Works like needed. Awesome, thanks for the help Wigi.

 

[wigi]

You're welcome.

 

[Kahuna8372]

I spoke too soon... it work good in my test workbook but I think I murdered it when I moved it to my working book. The results table is in its own sheet and all the data is copied into a different worksheet. I thought I updated the formula correctly to adjust for that... but I get the "Value" error. Here's what I changed it to:

=COUNTIFS('AVAILABILITY MASS ADMIN'!$C$2:$C$300,"Available",INDEX('AVAILABILITY MASS ADMIN'!$G$1:$M$1,,MATCH(C$1,'AVAILABILITY MASS ADMIN'!$G$1:$M$1,0)),TRUE,'AVAILABILITY MASS ADMIN'!$E$2:$E$300,"<="&$A2,'AVAILABILITY MASS ADMIN'!$F$2:$F$300,">="&$B2)

Availability Mass Admin being the sheet that the data is exported to from the other program (no change to format from original post). What part did I murder?