Really frustrated with count

**Teqsand** · 10-04-2010, 05:51 PM

If anyone can help me I would be eternally greatful...

I have a column (k) with dates
I have another column (r) with scores

What I need is a formula that counts all the cells in column R that are less that 75 AND have a date in column K that is after 7/1/2010

do not work
=COUNTIF(K4:K1000,">7/1/2010")*(R4:R1000<75)
=COUNT(($R$4:$R$936<75)*($K$4:$K$936<VALUE("7/1/2010")))
=SUMPRODUCT(($K$4:$K$936>"$H$1")*($R$4:$R$936<75))
I'm just frudtrated, I've tried them as an array nothing works....

**daddylonglegs** · 10-04-2010, 05:53 PM

I take it that H1 contains 7/1/2010.

You're nearly there with the last one, you shouldn't have quotes around "$H$1" otherwise it'll be treated as text rather than a cell reference, try

=SUMPRODUCT(($K$4:$K$936>$H$1)*($R$4:$R$936<75))

**Teqsand** · 10-04-2010, 06:30 PM

Originally Posted by daddylonglegs

I take it that H1 contains 7/1/2010.

You're nearly there with the last one, you shouldn't have quotes around "$H$1" otherwise it'll be treated as text rather than a cell reference, try

=SUMPRODUCT(($K$4:$K$936>$H$1)*($R$4:$R$936<75))

I tired that also, and it comes up with a "0" and I know the number should be higher based on what I have in the spreadsheet...

Se why it is so frustrtrting....

**daddylonglegs** · 10-04-2010, 06:35 PM

Well that looks like the formula you need so I think you have some issues with the data, perhaps some of the "dates" in column K or numbers in column R are text formatted. What happens if you try this variation?

=SUMPRODUCT(($K$4:$K$936+0>$H$1+0)*($R$4:$R$936+0<75))