SOLVED

# Count the number of dates based on a condition

Occasional Contributor

# Count the number of dates based on a condition

Hello,

hope you are doing good.

I have a pivot table with dates in a column. Using conditional formating i set the dates that are between today and up to 329 away as green , those that between 330 and 365 to be orange and more than 365 as red:

green  =AND(TODAY()-\$H19>=0,TODAY()-\$H19<329)

orange  =AND(TODAY()-\$H19>=330,TODAY()-\$H19<365)

red  =AND(TODAY()-\$H19>=365)

since this is conditional formatting i apply that to my whole column H and for all my dates i have some green, some orange and some red...

Now i'm trying to count the number or green, orange and red...and that's where i'm stuck...

I think i understand i have to use a COUNTIF and i wrote :  =COUNTIF(H:H,"<=" & TODAY()-329) ...this gives me 67   so it's... 67 dates that are smaller than 329 date ago ...if i'm not mistaken, but i don't understand how to have the correct count for each color.

Would someone please be able to help me on this ?

3 Replies
best response confirmed by Kaddrik (Occasional Contributor)
Solution

# Re: Count the number of dates based on a condition

Green:

=COUNTIFS(H:H,">"&TODAY()-329,H:H,"<="&TODAY())

Orange:

=COUNTIFS(H:H,">"&TODAY()-365,H:H,"<="&TODAY()-330)

Red:

=COUNTIFS(H:H,">0",H:H,"<="&TODAY()-365)

# Re: Count the number of dates based on a condition

Hello @Hans Vogelaar