• 397K Members
• 8,498 Online
• 434K Conversations
SOLVED

Deleted
Not applicable

# How to count and sum "Condtional formatting" cells by color in Excel 2010?

I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.

Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )

My question is: Is there any functional code (or other aproach) which can count and sum all cells by "Conditional formatted" colors, on one sheet, and that the generated result is linked through common formulas? That can be updated/refreshed/code run during the work process as a complete overview of colors of a matrix (and not by picking every color every time as I mentioned).

101 Replies

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi, this kind of question is better communicated with a document, stating the problems in short form in the worksheet. Thanks for sharing...

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi,

This is the solution:

## Reference

How to count and sum cells based on background color in Excel?

Solution

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

I came across this post being unanswered. so here is a User Defined Function in VBA

to SUM

use this UDF

```Function SumConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End Function```

if you want to Count instead of SUM then use the below UDF

```Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function
```

these solutions were provided to the similar question asked by other Excel users and worked for them.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

I will try the explained method to finally resolve my issue.

Thanks and have a great day !

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

You are most welcome. Thanks for the feedback.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

Thank You for posting this solution, I hope that this will also solve my problem With counting the colored cells when using conditinal formatting.

However, so far I am not able to get a number Count, I only get "NO-COLOR". E.g. =CountConditionColorCells(E2;E2:E5) or =CountConditionColorCells(E2:E5;E2) Perhaps I'm completely far out... Could You please let me know how the formula should be written?

Regards,

Julie

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Julie,

please see attached workbook sample that shows how you can use the UDF.

please post back, if you have any question.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

Not sure why it is not working on my computer, because after pressing "edit" on Your document, it also got error value "#Name?", and You can see on my document Attached how it looks with error #VALUE! I wasn't able to attach the Excel file With Makro enabled, but have of course had the makro enabled file so far.

Spoiler

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Julie,

it was not working because A) you need to enable macros in order to be able to use the UDF.

B)  the sample file you upload, you had only cells for criteria colored, but there wasn't any conditional formatting set in your workbook, that's why It did not work.

I have recorded a video that shows how you can set up your conditional formatting.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Thank you so much Jamil!

It seemed like something was actually wrong with my original dropdown list, so took some time for me to understand why it didn't work. But now it is finally ok :)
All the best!

You are welcome.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

I have tried to use the SUM formula here, but keep getting "NO COLOR"...I read through all your posts and tried to troubleshoot, but can't figure out what I'm doing wrong...can you take a look for me?

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Michelle,

There is a longer thread on the same UDF, there are multiple things that can cause the return of "no color"

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Please look at my attachment and my problem at sheet ZONE_WK25(CountColor) and at  column AV10 =countconditioncolorcells(\$D\$10:\$AS\$10,AV5)

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

null null

you asked the question in two places, so i replied here on why the UDF did not work for you.
please see my post here. https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...
Highlighted

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

the code doesnt work with my file..can you help me?

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Haziyatul,

I checked your file. The range where the conditional formatting is applied and the range where the UDF uses have to be the same and they are not the same in your excel file.

CF uses \$M\$9:\$EA\$56 and UDF uses X9:Y13

so to make this work, you need to either exactly set CF for each of the vertical lines.
however, you would not need to use the UDF for this, you can simply achieve the same result by using the SUMIF or SUMPRODUCT formula. you can see the examples in my earlier posts in the same thread.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Thank you Jamil. but this function doesn't work in case of "Top 10 Item" conditional formatting. Do you have any idea for my problem?

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi aa aaaa,

Please read my earlier messages, the UDF only works if the conditional formatting is set using formula.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Is there any solution for my problem?

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Open a new question not on this thread but a new question with sample dummy of your data.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

How can i open a new question?

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Thank you very much.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hello.. can you solve the problem for attached file?

I want to count the specific text colored by conditional formatting column wise. Conditional formatting done by formula.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Please read my earlier messages on the thread and use the UDF to be able to count the conditionally formatted cells.

okay

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

you have a new question, so the experts would likely to answer your question over there.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

I had a similar issue to Julie.

I did exactly what you said and the document worked.

Since saving, closing the document and today opening it the formula doesn't seem to work when calculating the colored cells.

I get the '#NAME' error.

Any assistance you can offer would be appreciated...?

Document attached.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Rowan,

You need to save as macro enabled workbook, when you save as normal workbook format the UDF disappears.

Use either macro enabled workbook format on save as it binary format. Then it will work.

You need to paste the code again into the workbook and save as then the Name error will fix.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

I have tried your formulas and they do not work.  I have been stuck on this for 3 weeks now and I'm trying to count my cells as they turn certain colors from Conditional Formatting rules I put in place for my drop down menu's.  If you would be able to assist me that would be wonderful because I am getting tired of watching and reading videos on how it wont work for me formulas.

Matthew

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Mathew,

I looked at your file. You have set up the conditional formatting using the = cell value option .

The UDF only works if the conditional formatting is set using formulas not through the built-in option of conditional formatting.

https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

What you are trying to do is possible, but you have set many conditional formatting rules.

I can help you if you give me the list of the text that are for green color and the list of the texts that are for red color.

these are all of the list and if you can give me which one of them are for green and which one for red. I can build the formula for you.

Acceptable Use Policy
Access Control List
Access Point
Annualized Loss Expectancy
Annualized Rate of Occurrence
Anti-virus
Application Programming Interface
Application Service Provider
Attribute-based Access Control
Authentication-Authorization-Accounting
Basic Input/Output System
Bridge Protocol Data Unit
Certificate
Certificate Authority
Challenge Handshake Authentication Protocol
Chief Information Officer
Cipher Block Chaining
Cipher Feedback
Closed-Circuit TeleVision
Common Access Card
Completely Automated Public Turing Test to Tell Computers and Humans Apart
Computer Emergency Response Team
Computer Incident Response Team
Content Management System
Contingency Planning
Continuity of Operations Plan
Controller Area Network
Corporate Owned-Personally Enabled
Corrective Action Report
Counter-Mode/CBC-Mac Protocol
Cyclical Redundancy Check
Triple Digital Encryption Standard

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

These are all the acronyms I am trying to achieve on my spreadsheet, thank you for your assistance

Matthew

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Mathew,

I did not understand your question. The attachment is list of acronyms in pdf.
your Excel file which you posted earlier does not contain of all of these.
you question was related to counting the green and red and to make this work, you need to set up the conditional formatting by yourself. then you can give me the list of the acronyms that are in green category and also the list which are in red category. by then i will be able to give you a formula that handles that count for red and green. Right now, I have not received sufficient information related to your excel problem.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil

I stumbled upon your code as I am using conditional formatting formula to highlights my cell. Now I want to count the highlighted cells.

Similar to Julie, the VBA returns "no color" instead of the numbers. I have done everything that I can possibly can.

I suspect it doesn't work because I use this formula in my conditional formatting =if(isblank(\$K\$1),0,(search(\$K\$1,A5))). A5 is where my data starts. K1 is where when someone type a text and that text is in the data, it will highlight those texts. Attached is an example (which didn't quite work but you'll get the idea).

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

hi 7 Heven,

You have used Search function and also whole column reference A:A which is not compatible with the UDF.   I have modified the formula and range in the attached example and the UDF works.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil

I can't believe my luck that you actually replied to my query! Thank you very much!

If I modified the formula as per your suggestion and remove the search function, it doesn't work on my master spreadsheet. Is there a way to modify the UDF to make it work with search function?

Many thanks again.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil I was able to successfully use your code to count my conditionally formatted cells but my data changes regularly and the function doesn't automatically refresh. In order for it to pick up the changes i have to refresh the cell with F2 and then enter, any solution for that?

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

7_Heaven,
you can use search function, but your ranges should not be whole column reference. I have shown in the example in my previous post how you can use it.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

mecerrato
first try to check that your workbook calculation setting is "Automatic"
if it is automatic and still did not work then try putting the below line after the first line of UDF.

Application.Volatile

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)

Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)

I have attached a test sheet with the structure.

Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.

Alin

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)

Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)

I have attached a test sheet with the structure.

Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.

Alin

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Alin,

I checked one of your file and it is clear that you did not use the option of CF "- Use a formula to determine which cells to format"    the UDF only works if you used formula to determine which cells to format.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

I have used one of the worksheets that i found on this topic, on which the count by color worked, and i adapted it for my example.

i have used a formula in conditional formatting in order to highlight all the cells that contain "TEXT".

But somehow it is showing only a count of three for all the three rows that i used for example.

I missed something for sure.

Attached the worksheet.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

hi Alin,

I looked at your file.  The issue was that you had CF applied range A10 to A10:E12  while your UDF were using for each row.  the UDF range and the CF range should match, that is when it will work.

I have attached the workbook and also i have attached a recorded video on how to use the paintbrush to apply CF to the other cells without the hassle of recreating CF for each row.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

thank you so much for this Jamil

I got it finally :)

I wish you all the best

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

Please check my attached file which i want to count the colored CF cells by row. Problem is there are different formulas in each column, if i want to count the result by rows, i get an error. If i count by column wise it got results. Please help so i can count it by rows.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Roland,

If you read through my earlier posts. I have clearly mentioned that UDF only works if the conditional formating is set using Formula. you have used formulas and non-formula options to set your conditional formatting. that is the first issue that is causing the UDF not to work.

the second issue is that your are using the whole column references in your CF which is not going to work.

third issue is that your CF is set by column and you are trying to do the count in UDF using row. If you look at the example I illustrated in answer to Alin. you can see that i modified the CR using painbrush to reflect the rows.

so you need to fix the three of the issues i pointed, so that the UDF would work.

# Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

Hi Jamil,

The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.

Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.

I followed all the steps that you suggested (i hope i didn't missed something out).

I have attached the worksheet.

Alin

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
205 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies