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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]Status
[/TD]
[TD]From
[/TD]
[TD]To
[/TD]
[TD]Sun
[/TD]
[TD]Mon
[/TD]
[TD]Tue
[/TD]
[TD]Wed
[/TD]
[TD]Thu
[/TD]
[TD]Fri
[/TD]
[TD]Sat
[/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]Available
[/TD]
[TD]07:00
[/TD]
[TD]17:00
[/TD]
[TD]False
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]False
[/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]Available
[/TD]
[TD]05:00
[/TD]
[TD]20:00
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[/TR]
[TR]
[TD]Employee 3 etc...
[/TD]
[TD]Available
[/TD]
[TD]04:00
[/TD]
[TD]04:00
[/TD]
[TD]True
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Daypart
[/TD]
[TD="align: center"]Sun
[/TD]
[TD="align: center"]Mon
[/TD]
[TD="align: center"]Tue
[/TD]
[TD="align: center"]Wed
[/TD]
[TD="align: center"]Thu
[/TD]
[TD="align: center"]Fri
[/TD]
[TD="align: center"]Sat
[/TD]
[/TR]
[TR]
[TD]5am-11am
[/TD]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11am-2pm
[/TD]
[TD="align: center"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?