Using Excel 2010, attempting to use COUNTIF with Date qualifier & multiple conditions

[scapaflow]

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Sched Sick[/TD]
[TD]1/3/13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Sched Sick[/TD]
[TD]1/3/13[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Unsched Sick[/TD]
[TD]1/3/13[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Unsched Sick[/TD]
[TD]1/3/13[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Sched Pers[/TD]
[TD]1/3/13[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Unsched Pers[/TD]
[TD]1/10/13[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Sched Pers[/TD]
[TD]1/10/13[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Sched Sick[/TD]
[TD]1/10/13[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Sched Vac[/TD]
[TD]1/10/13[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Unsched Vac[/TD]
[TD]1/17/13[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Unsched Pers[/TD]
[TD]1/17/13[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Unsched[/TD]
[TD]1/17/13[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Sched Sick[/TD]
[TD]1/17/13[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Sched Vac[/TD]
[TD]1/17/13[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 250, align: right"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]1/3/13[/TD]
[TD]1/10/13[/TD]
[TD]1/17/13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Scheduled Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Unscheduled Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Combined Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]














Hello! I am attempting to do that which could very easily be done with a pivot table but instead I have manually set up a chart instead for graphing that would take a lot of work to redo - the spreadsheet I am using relies on an import that I do not want to adapt further. I am attempting to set up the counting of instances of various types of absences based on the date and several different names of time off.
I would like the formula to reference the range B:B to see if it matches a cell with the given date in it. If it does, count all instances of specific time - Sick, Personal, Vacation, Unpaid - and provide a total count for each type. If it does not match the date, do not count it.

What would be optimal would be to allow the import of data into the spreadsheet and a simple update for the date in the single cell to allow the COUNTIFS to work and add up the instances for each type. Again this is very much like a pivot table, but because of the design of the spreadsheet it would be more work to change it for how it's integrated.

I have tried IF and COUNTIF statements in cell G2, =IF(D:D=G1,COUNTIF(C:C,"Sched Sick")*COUNTIF(C:C,"Sched Pers")*COUNTIF(C:C,"Sched Vac"))
And COUNTIF - =COUNTIF(A:A,"Sick")*COUNTIF(A:A,"Personal")*COUNTIF(A:A,"Unpaid"), but these didn't work.

The formula for G2:I2 should count all instances of "Sched Sick", "Sched Pers", and "Sched Vac" ONLY if the date in range D:D equals cell G1. It should not count any other value.
The formula for G3:I3 should count all instances of "Unsched Sick", "Unsched Pers", and "Unsched Vac" ONLY if the date in range D:D equals cell G1 and it should not count any other value.

The totals for G2 should be 3
The totals for G3 should be 2
The totals for H2 should be 3
The totals for H3 should be 1
The totals for I2 should be 2
The totals for I3 should be 3


If I import more data for a given date, I should be able to add the date to the next column (it would be J2 on my spreadsheet) and it tabulates the totals automatically.

Any help would be greatly appreciated, thank you in advance for any help!

 

[smkyle]

how about
E2=LEFT(C2,1)&D2
copy down
G2=COUNTIF($D$2:$D$999,LEFT($F2,1)&G$1)
copy down and across

 

[scapaflow]

how about
E2=LEFT(C2,1)&D2
copy down
G2=COUNTIF($D$2:$D$999,LEFT($F2,1)&G$1)
copy down and across

Thank you so much for taking a look and helping!

I tried this and entered ($E$2:$E$999, ...) and it worked! Awesome! But I was really looking for a way to get almost an array or sumproduct solution to this that includes the reference to the G1:I1... for the date. And I was trying to get a countif solution that only counts the types of absences like "Sched Sick" or "Unsched Sick" get counted based on my setting up the reference. Because our absence types change periodically I was hoping to be able to just add / edit the array to include new ones and keep the old ones for a cumulative count.