COUNT Function Help

[greeneggsandsam]

I have the following formula to count the number of COMPLETE records that contain dates that fall within a given range..
=SUMPRODUCT(--($N$2:$N$500<=$Q$1), --($N$2:$N$500>=$P$1), --(COUNTIF($R$1:$S$1, $M$2:$M$500)))
Where N2:N500 is the date the records were entered
Where M2:M500 is recorded as either COMPLETE or PENDING
Where P1 = September 1st, 2012
Where Q1 = today()
Where R1 = COMPLETE
Where S1 = PENDING

This formula works fine for calculating the number of "COMPLETE" records within the specified dates.
Now maybe I am overthinking this but I also want to calculate the number of "PENDING" records within the same date range.
I thought it should have been as easy as flipping the formula around but no.
I can easily have it calculate the number of BLANK cells within a given range as any cells with "PENDING" in M2:M500 would also contain blank values in N2:N500. The probelm there though is I have to expand the formula as I go in and entered completion dates.

Long story short I want to be able to calculate the number of either BLANK cells in N2:N500 within a given date range or the number of "PENDING" cells in M2:M500 within a given date range.

Thanks

[Jonmo1]

Try

For Complete
=SUMPRODUCT(--($N$2:$N$500<=$Q$1), --($N$2:$N$500>=$P$1),--($M$2:$M$500=$R$1))

For Pending
=SUMPRODUCT(--($N$2:$N$500<=$Q$1), --($N$2:$N$500>=$P$1),--($M$2:$M$500=$S$1))

[greeneggsandsam]

I thought that would have worked too but no such luck.

[Jonmo1]

In what way did it not work?
do you get an error? What error?
Does it give wrong result? What result DID it give? What result did you expect, and why?

Can you post a sample book?

[greeneggsandsam]

It is still giving me a zero value when it should be reporting 12.
Sample book attached.
Thanks in advance for your help.

[greeneggsandsam]

I have highlighted J2 as the cell where the formula is located.
As you can see records 1 to 16 all have target dates scheduled before today but only records 3, 7, 11 and 16 have been completed as indicated in cell I2 therefore J2 should be reporting 12.

[Jonmo1]

0 is the correct answer because there are 0 DATES in Column E that are Between G1 and H1 with the word PENDING in column D.

Blanks do not count as dates.

[Jonmo1]

OK, I see it now.

for PENDING, you need to check for dates in column C not E..
Although there are actually 11 not 12. Record 1 is in the year 2008, definately not between 2012-2013

So for Complete
=SUMPRODUCT(--($E$2:$E$300<=$H$1), --($E$2:$E$300>=$G$1), --($D$2:$D$300=$I$1))

And for pending
=SUMPRODUCT(--($C$2:$C$500<=$H$1), --($C$2:$C$500>=$G$1),--($D$2:$D$500=$J$1))

[greeneggsandsam]

Is there an emoticon for epic face palm? I need one. lol
Thanks for your help

[Jonmo1]

Great, glad it's worked out.