SOLVED

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

Deleted
Not applicable

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).
 
Thanks in advance!

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

Hi,

 

This is the solution:

ColorFunction.GIF

 

Reference

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

best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

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.

 

For more detail here is the link for that answer 

Thank you very much for you kind and very helpful answer.

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

Thanks and have a great day !

You are most welcome. Thanks for the feedback.

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

 

Hi Julie,

 

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

 

please post back, if you have any question.

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.

 

Great if you can advice.

 

Spoiler
 

 

Hi Julie,

 

I checked your file.

 

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.

 

please download the attached video and watch it. also i have attached your workbook with conditional formatting set in it.

 

 

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!

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?

Hi Michelle,

 

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

 

Please read the limitations of the UDF and other comments here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

 

 

haii i already try this but not successful la.. please help me.

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

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

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

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.

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?

Hi aa aaaa,

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

Accepted Solutions
best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

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.

 

For more detail here is the link for that answer 

View solution in original post