View Full Version : Solved: Countif
vzachin
08-28-2006, 10:19 AM
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
08-28-2006, 10:52 AM
Hi Zach,
You could use sumproduct:
=SUMPRODUCT((D5: D1000>TODAY()-5)*(D5: D1000<TODAY())*1)
Matt
vzachin
08-28-2006, 10:58 AM
thanks Matt. i was wondering about SUMPRODUCT but couldn't get the correct syntax.
This works like a charm!
Bob Phillips
08-28-2006, 11:23 AM
A (slightly) more efficient formula
=COUNTIF(D5:D1000,">"&TODAY()-5)-COUNTIF(D5:D1000,">="&TODAY())
Bob Phillips
08-28-2006, 11:23 AM
Hi Zach,
You could use sumproduct:
=SUMPRODUCT((D5: D1000>TODAY()-5)*(D5: D1000<TODAY())*1)
Matt
Why the *1, it is redundant.
mvidas
08-28-2006, 11:28 AM
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
08-28-2006, 06:48 PM
both formulas work though i like the countif formula more.
thanks again
Cyberdude
08-28-2006, 08:13 PM
Why do I see a blue smilie following each D5??
Is it the ":D" that is being rreplaced?
Bob Phillips
08-29-2006, 12:20 AM
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
08-29-2006, 12:26 AM
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")
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.