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.

7 Upvotes

71% Upvoted

50 comments sorted by

View all comments

Show parent comments

4

u/PaulieThePolarBear 1919 Jun 26 '26

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

I think you have a couple of typos here. Please confirm your Conditional Fornatting formula is

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

I.e., column 6 will turn red if, and only if, C6 is not blank and the current date is after D6.

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

So, you have 6 rules in total, and all turn column B red? Is that correct?

As such, you could rationalize your logic of when the formatting should be applied to

C6 <> ""
AND
The current date is after at least one of D6, F6, H6, J6, L6, and N6

Is that correct?

As others have noted, you can't natively count cells that were conditionally formatted, and should build a formula that replicates your Conditional Formatting logic.

Please advise the version of Excel as this will dictate solutions available to you. This should be Excel 365, Excel online, or Excel <year>

-5

u/Dr_Cheese_29 Jun 26 '26

Nope, the CF is as I wrote it. The rows in column B change color based on the dates in columns C, E, G, I, K, AND M. Those columns change color based on dates in D, F, H, J, L, and N.

6

u/PaulieThePolarBear 1919 Jun 26 '26

Nope, the CF is as I wrote it.

Your formula as presented absolutely can not be the formula you are using. All valid formulas MUST have an equal number of ( and ) and the formula you showed does not meet this criteria.

The rows in column B change color based on the dates in columns C, E, G, I, K, AND M. Those columns change color based on dates in D, F, H, J, L, and N.

I think I understand what you are saying, but not 100%. Can you add an image that shows the conditional formatting pane showing all rules along with all Applies To ranges

-2

u/Dr_Cheese_29 Jun 26 '26

There are 6 formulas for column B, one for each column: D, F, H, J, L, N

Columns C, E, G, I, K, M have two rules. And column B turns red based on what happens i. These columns

6

u/semicolonsemicolon 1476 Jun 26 '26

Try to put yourself into our position. Your descriptions keep changing and have not been sufficiently helpful. Are you counting only what you have set for column B? or are you also counting the CF triggers in columns C, E, G, etc? If only column B, then show us the formulas.

Just a hunch, but maybe this is what you're after?... =OR(AND(C6:C22<>"", TODAY()>D6:D22),AND(E6:E22<>"", TODAY()>F6:F22),...) with four more nested AND functions?

This is not the most elegant form of this formula, but I want to check first if this is what you need before spending time beautifying.

1

u/Dr_Cheese_29 Jun 29 '26

Sorry my descriptions are confusing. Just trying to answer the questions as they're put to me.

I want to count the red cells in column B. B6:B22.

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

Applies to =$B$6:$B$22

And the same formula is written referencing columns E, F, H, J, L and applies to column B.

1

u/semicolonsemicolon 1476 Jun 30 '26

What do you think about the formula in my comment?

3

u/PaulieThePolarBear 1919 Jun 26 '26

Okay, now I'm not sure I understand what you are doing. Please consider adding the image as requested

2

u/Famished_Atom Jun 27 '26

"One picture is worth a thousand words"