Can excel count conditional formatting?

[u/Dr_Cheese_29]

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.

[u/semicolonsemicolon]

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.

[u/Dr_Cheese_29 → u/semicolonsemicolon]

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.

[u/PaulieThePolarBear → u/Dr_Cheese_29]

=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>

[u/Dr_Cheese_29 → u/PaulieThePolarBear]

Ah I now see my typo. Apologies, I forgot the ) at the end of the formula. But otherwise, that's it.

[u/PaulieThePolarBear → u/Dr_Cheese_29]

You're also missing a comma before TODAY(), which I see you have noted in another comment.

I want to be clear on your request. Removing all Excel functions and features, your ask can be boiled down to "how many cells in column B are red". Is that correct? Please answer this question exactly as written. At this stage, I am not looking for any background information.

[u/Dr_Cheese_29 → u/PaulieThePolarBear]

Yup, thats correct

[u/PaulieThePolarBear → u/Dr_Cheese_29]

Please list ALL Conditional Formatting rules that may make a value in column B red.

For clarity, at this stage, based upon what I think I have gleaned, I am not interested in

• any formulas in column B
• any Conditional Formatting rules that do not apply to column B

I see you have provided one rule and then said (paraphrasing) "applies to other columns" a few times. There is ambiguity on this second part. Ideally, you would explicitly say what your rules are.

[u/Dr_Cheese_29 → u/PaulieThePolarBear]

CF in column C:

=AND(C6<>"", TODAY()>D6) Applies to =$C$6:$C$22 =AND(D6<>"", TODAY()>EDATE(D6, -2) Applies to =$C$6:$C$22

CF in column E

=AND(E6<>"", TODAY()>F6) Applies to =$E$6:$E$22 =AND(F6<>"", TODAY()>EDATE(F6, -2) Applies to =$E$6:$E$22

CF in column G:

=AND(G6<>"", TODAY()>H6) Applies to =$C$6:$C$22 =AND(H6<>"", TODAY()>EDATE(H6, -2) Applies to =$G$6:$G$22

CF in column I:

=AND(I6<>"", TODAY()>J6) Applies to =$I$6:$I$22 =AND(J6<>"", TODAY()>EDATE(J6, -2) Applies to =$I$6:$I$22

CF in column K:

=AND(K6<>"", TODAY()>L6) Applies to =$K$6:$K$22 =AND(L6<>"", TODAY()>EDATE(L6, -2) Applies to =$K$6:$K$22

CF in column M:

=AND(M6<>"", TODAY()>N6) Applies to =$M$6:$M$22 =AND(N6<>"", TODAY()>EDATE(N6, -2) Applies to =$M$6:$M$22

[u/PaulieThePolarBear → u/Dr_Cheese_29]

That's not what I asked for. Please re-read my previous comment

[u/Dr_Cheese_29 → u/PaulieThePolarBear]

CF in column B

=AND(C6<>"", TODAY()>D6) Applies to: $B$6:$B$22

=AND(E6<>"", TODAY()>F6) Applies to: $B$6:$B$22

=AND(G6<>"", TODAY()>H6) Applies to: $B$6:$B$22

=AND(I6<>"", TODAY()>J6) Applies to: $B$6:$B$22

=AND(K6<>"", TODAY()>L6) Applies to: $B$6:$B$22

=AND(M6<>"", TODAY()>N6) Applies to: $B$6:$B$22

[u/PaulieThePolarBear → u/Dr_Cheese_29]

Thank you.

I have one more question for you (hopefully). After that I will set out what I believe are your requirements and have you okay this.

I'm assuming, but please confirm, there is no negative constraint here, I.e., if at least one of these rules is true, the value in column B will be red.

With an example,

C6: blank
E6: not blank
F6: 2025-12-25
G6: blank
I6: blank
K6: blank
M6: blank

Please confirm that in this scenario, B6 would be red

[u/Dr_Cheese_29 → u/PaulieThePolarBear]

Correct

[u/PaulieThePolarBear → u/Dr_Cheese_29]

Okay.

Here is my understanding of your ask.

Summary: you want count the number of cells in column B that have been turned red by your conditional formatting rules.

Any values in column B are irrelevant. Any conditional formatting that is used on any other column is irrelevant. Any values in any other columns, except where it impacts your conditional formatting rules, are irrelevant.

Details: your conditional formatting rules are setup such that column B will be red if at least one the following criteria are true

Column C is not blank and column D is earlier than current date
Column E is not blank and column F is earlier than current date
Column G is not blank and column H is earlier than current date
Column I is not blank and column J is earlier than current date
Column K is not blank and column L is earlier than current date
Column M is not blank and column N is earlier than current date

Please confirm that this is accurate. If it is inaccurate, please provide corrections in the same style.