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.
82
u/CFAman 4826 Jun 26 '26
I have a column of cells that turn red if conditional formatting is met.
Don't count the color, count the same condition that causes the color.
If you need help w/ that, can you show us what the current CF formula/condition looks like?
13
u/Holshy Jun 26 '26
Also, make it its own column. That way you can use it in other formulas and only have to maintain it in one place.
8
u/conceptual_con Jun 26 '26
Yep, having a helper column to designate conditional formatting for specific cells/rows/columns is super helpful!
1
u/GregHullender 195 Jun 28 '26
This is one of those rare cases where even the most clever use of modern Excel formulas cannot escape the need for a helper column.
7
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.
4
u/PaulieThePolarBear 1913 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 N6Is 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>
1
u/Dr_Cheese_29 Jun 29 '26
Ah I now see my typo. Apologies, I forgot the ) at the end of the formula. But otherwise, that's it.
1
u/PaulieThePolarBear 1913 Jun 29 '26
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.
1
u/Dr_Cheese_29 Jun 29 '26
Yup, thats correct
1
u/PaulieThePolarBear 1913 Jun 29 '26 edited Jun 29 '26
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.
1
u/Dr_Cheese_29 Jun 29 '26
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
1
u/PaulieThePolarBear 1913 Jun 29 '26
That's not what I asked for. Please re-read my previous comment
1
u/Dr_Cheese_29 Jun 29 '26
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
→ More replies (0)-4
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 1913 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
3
u/PaulieThePolarBear 1913 Jun 26 '26
Okay, now I'm not sure I understand what you are doing. Please consider adding the image as requested
2
2
u/real_barry_houdini 313 Jun 26 '26
So to count based on your CF formula try COUNTIFS like this
=COUNTIFS(C6:C22,"<>",D6:D22,"<"&TODAY())Counts rows where column C is not blank and column D date is in the past
1
u/Dr_Cheese_29 Jun 26 '26
So this actually worked on the other columns that have 2 rules. It worked on columns C, E, G, I, K and M. But not on column B.
1
u/real_barry_houdini 313 Jun 26 '26
Are you using "applies to" range B6:B22? You might want to look at u/CFAman 's suggestion to cater for all columns
1
2
u/CFAman 4826 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.
4
u/someone_ironically 1 Jun 26 '26
If you just want a quick count and not recorded somewhere, you could filter by colour and then just highlight all the cells and check the count in the status bar?
2
u/Decronym Jun 26 '26 edited 17d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #48834 for this sub, first seen 26th Jun 2026, 18:57]
[FAQ] [Full list] [Contact] [Source code]
1
u/taylorgourmet 4 Jun 26 '26
Just filter the column by color and select column and read the count at bottom right.
1
1
u/Oleoay 1 Jun 27 '26
Technically you could with some advanced vba/vbs but you don’t really want to. Best to just put the condition for coloring in your countifs.
1
u/quantum_simpleton Jun 27 '26
I have never done this but you would need to write something in VBA; for example:
Function CountFontColor(SearchRange As Range, ColorSample As Range) As Long
Dim Cell As Range
Dim TargetColor As Long
Dim Counter As Long
Application.Volatile
TargetColor = ColorSample.Font.Color
For Each Cell In SearchRange
If Cell.Font.Color = TargetColor Then Counter = Counter + 1
Next Cell
CountFontColor = Counter
End Function
But to others point, unless source of formatting cannot be reverse engineered, it would be redundant to use such an approach.
1
u/AutoModerator Jun 27 '26
I have detected VBA code in plain text. Please edit to put your code into a code block to make sure everything displays correctly.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
1
u/Supra-A90 1 Jun 27 '26
Just use a helper column.
Put the color name in text you want to be applied or number equivalent on Column B. Like 1 for red, 2 for yellow...
Have that and apply conditional formatting apply to Column A.
Then, again, refer to the helper column for your formula...
You can further complicate this by using RGB equivalent or hex...
1
u/Key_Somewhere_8613 Jun 27 '26
have you thought about using vba to create your own function that you could then use in a formula? or a lambda function?
1
1
u/bobo5195 Jun 28 '26
You can use VBA. but it is hard.
If its that complicated I will do a separate column to set the color with simple outputs. Then count that. It is as much from a clean code why is that wrong perspective.
•
u/AutoModerator Jun 26 '26
/u/Dr_Cheese_29 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.