Mar 16 2022 05:02 AM
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 ?
Thanks in advance
Mar 16 2022 05:12 AM
SolutionGreen:
=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)
Mar 16 2022 05:22 AM
Mar 16 2022 07:52 AM
You could change H:H to the relevant part, for example H19:H100. The formulas will be more efficient too.
Mar 16 2022 05:12 AM
SolutionGreen:
=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)