 # CountIF + Expressions

• I am trying to count the number of cells with a date within the last 7 days. CountIF supposedly supports expressions, but I cannot get it to recognize the following:
COUNTIF(J1:J80,"&gt;(Today()-7)")

The function will will work if I enter the date I want to see if the field is greater than =COUNTIF(J14:J31,"&gt;01/01/2003"), but I want the date to change for the current day...

I've got a solution that works, but it involves setting a cell to Today()-7 and then entering: =COUNTIF('1.4A'!J2:J31,A9) + COUNTIF('1.4A'!J14:J31,A10) + COUNTIF('1.4A'!J14:J31,A11) + COUNTIF('1.4A'!J14:J31,A12) + COUNTIF('1.4A'!J14:J31,A13) + COUNTIF('1.4A'!J14:J31,A14+'1.4A'!G58) + COUNTIF('1.4A'!J14:J31,A15) + COUNTIF('1.4A'!J14:J31,A16)

but I don't like this impletmentation....

• Hi, you wrote:

"but I don't like this impletmentation...."

Wow, can't blame you for that!!

Try this syntax based on your original formula:

=COUNTIF(J1:J80,"&gt;"&TODAY()-7)

Tom Urtis

• The following works for me (win98, O2k)
=COUNTIF(A1:A8,"&gt;"&TODAY()-7)
Regards, Manfred

• Thank You, that worked vunderfully.... • One more thing and then I'll take a refresher course.... How can I make Excel count the cells as described in the original post AND only count them if another cell has a value.. So... COUNT J1:J80 WHERE J1:J80=Today()-7 AND F1:F80=Pending

so only count the cells that are within the last seven days and Pending.

• Quote

Originally posted by Anonymous
One more thing and then I'll take a refresher course.... How can I make Excel count the cells as described in the original post AND only count them if another cell has a value.. So... COUNT J1:J80 WHERE J1:J80=Today()-7 AND F1:F80=Pending

so only count the cells that are within the last seven days and Pending.

for multiple criteria, you'll probably need =SUMPRODUCT (for ease) or need to take a look at Excel's Database functions (all proceeded with D.... ie DCOUNTA, DSUM etc etc) if you want some serious info without performance degredations...

however : sumproduct :

=SUMPRODUCT((J1:J80=TODAY()-7)*(F1:F80="Pending"))

for an ameuter explanation of what this formula is actually doing in the background, see the earlier thread :