SOLVED

Highlighted
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).

155 Replies
Highlighted

# 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...
Highlighted

# 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?

Highlighted
Best Response confirmed by Jamil Mohammad (Super Contributor)
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.

Highlighted

# 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 !

Highlighted

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

You are most welcome. Thanks for the feedback.

Highlighted

# 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

Highlighted

# 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.

Highlighted

# 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

Highlighted

# 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.

Highlighted

# 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!
Highlighted

You are welcome.
Highlighted

# 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?

Highlighted

# 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"

Highlighted

# 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)

Highlighted

# 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?

Highlighted

# 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.
Highlighted

# 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?

Highlighted

# 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.