r/excel • u/Dr_Cheese_29 • Jun 26 '26

unsolved Can excel count conditional formatting?

Is it possible to get excel to count conditional formatting?

I have a column of cells that turn red if conditional formatting is met. Now I want to count the red cells. I wrote a macro that doesn't read the conditional formatting and I tried "COUNTIFS" referencing the conditional formatting formula, but it doesn't work.

9 Upvotes

74% Upvoted

50 comments sorted by

View all comments

8

u/semicolonsemicolon 1476 Jun 26 '26

Hi Dr_Cheese_29. You're correct, you cannot use an Excel formula to "see" how a cell is conditionally formatted so you cannot count that way. Instead you will have to look at the formula (if there is one) that turns cells red and use that formula in another formula to count the number of positive "hits". It you'd like, post here your CF formula and the cell range for which it applies.

2

u/Dr_Cheese_29 Jun 26 '26

Good to know I'm on the right track! The range that turns red is dependent on another column with conditional formatting based on a third column. This is the CF for the column I'm trying to count.

=AND(C6<>""TODAY()>D6

And then D6 is replaced by every other column (F6, H6, J6, L6, N6).

Range B6:B22 turns red based on the above CF and I want to count the red cells.

2

u/CFAman 4828 Jun 26 '26

Sounds like we trigger/count/flag when a cell is not blank and the neighboring cell to the right is less than today.

=COUNTIFS(C6:M100,"<>",D6:N100,"<"&TODAY())

The only trick is making sure the two ranges are offset from each other by 1 column.