Solved: Countif

[vzachin]

Hi,

How can I rewrite the following formula
=COUNTIF(D5 : D1000,"=8/28/06")
to count dates from the past 3 days.


thanks
zach

[mvidas]

Hi Zach,

You could use sumproduct:
=SUMPRODUCT((D5: D1000>TODAY()-5)*(D5: D1000<TODAY())*1)

Matt

[vzachin]

thanks Matt. i was wondering about SUMPRODUCT but couldn't get the correct syntax.
This works like a charm!

[Bob Phillips]

A (slightly) more efficient formula

=COUNTIF(D5:D1000,">"&TODAY()-5)-COUNTIF(D5:D1000,">="&TODAY())

[Bob Phillips]

Hi Zach,

You could use sumproduct:
=SUMPRODUCT((D5: D1000>TODAY()-5)*(D5: D1000<TODAY())*1)

Matt

Why the *1, it is redundant.

[mvidas]

A (slightly) more efficient formula

=COUNTIF(D5:D1000,">"&TODAY()-5)-COUNTIF(D5:D1000,">="&TODAY())Good point, didnt think about just subtracting it

I put the *1 in it pretty much just for my own auditing purposes, that way I can look at the formula and immediately know I'm using it as a counting formula instead of summing values. Definately not necessary

[vzachin]

both formulas work though i like the countif formula more.
thanks again

[Cyberdude]

Why do I see a blue smilie following each D5??
Is it the ":D" that is being rreplaced?

[Bob Phillips]

Why do I see a blue smilie following each D5??
Is it the ":D" that is being rreplaced?
It is because the BB software is seeing the colon-D as a smiley, an d so translating it to the image. That is probably why Matt left a space between the colon and the second D in his response, syntactically incorrect, but doesn't show the smiley, and Excel automatically corrects it.

There was a thread here some time ago about how to avoid it, but we all know now (:D)

[Bob Phillips]

I put the *1 in it pretty much just for my own auditing purposes, that way I can look at the formula and immediately know I'm using it as a counting formula instead of summing values. Definately not necessary

I would have thought that each element testing a condition, and no straight range array tells you that. But you could actually add something to tell you so

=SUMPRODUCT((D5: D1000>TODAY()-5)*(D5: D1000<TODAY()))+N("counter only")