SOLVED

sum by color when colors are set by conditional formatting

Copper Contributor

i have a column of numbers that are color coded (to represent a specific mfg department) and I need to total the values by color.  Meaning I need to total all the values that have the same background color.  

 

I have done an exhaustive search online and was able to quickly find a way to sum by color, unfortunately it only worked for cells whose color was set manually (NOT using conditional formatting).  I have found a few references to VB code that should provide the functionality but I can't get any of them to run (except for the one that works for manually set colors).

 

I have control of the data that I'm trying to sum.  is there another method to "tag" values?  I thought of adding a letter prefix, but coulnd't find any way to sum a column of numbers that are contained in text strings.

 

Any help would be greatly appreciated!!

 

I'm using Office 365 (excel 2016) on a windows 10 machine

 

304 Replies

Hi Matt,

 

may be using the SUMIF or SUMIFS functions could help. You can define as criteria the same criteria you defined for the conditional formats. And, you don't need macros then. If you prefer macros, please have a look on this article from Ablebits. There is a section for adding values on conditional formats.

 

Best,

Mourad

@Deleted's suggestion of an extra helper column for a sumif is by far the safest and most easily understood option.     

Thank you for the response Mourad.  sorry i didn't see your post until Wyn responded.

 

I will try the sumifs function. I hadn't thought about burying the conditional formatting formula in the sumifs formula.  Maybe that will work.

 

best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

Hi matt nipper,

 

since you mentioned that you "have done an exhaustive search online" i have come up with a solution for you, although it has two limitations A) it will only work, if your rules of conditional formatting is created using conditional formatting rules with formula aka (use a formula to determine which cells to format) and B) the UDF will only work if sum range is more than one cell another word, it will not sum a single cell, as well as the conditional formatted range is more than one cell.

 

the example file, you can download it from here. I could not upload it here, as it is a Excel Macro-enabled Workbook that contains the 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

 

 

Capture.PNG

in the attached example file you can see that from A3:G16 cells are formatted using Conditional Formatting.

User Defined Function (UDF) is placed in cells J2 & J3 and cells I2 and I3 are the criteria color used as reference inside the UDF in J2 and J3.

 

I hope this helps you.  

 

 Edit: uploaded file and updated the code to the correct one.

Hello,

 

take a look at this question in the Microsoft Community formerly known as the "Answers" Q&A forum. (I know, the "Community" site name is confusing to have on two different sites).  

 

Make sure to look at all the replies to see how to get from conditional formatting to a Sumifs or Countifs formula.

Hi Jamil,

 

I came across your solution to a similar query i have, and tried applying it to my data.

 

It almost works however the summed total is slightly off the total when i manually sum the data.  it seems like it may be a rounding issue....do you have any suggestions on how to resolve?

 

Thanks in advance,

Micheal

Hi Michael,

 

there is no rounding issue. my suggestion is to revisit the colors and try to run the test on a smaller data set and see how it goes.    if you can provide a sample dummy example. i can take a look.

Hi Jamil,

 

Thanks for the quick response.  It definitely appears to be a rounding issue.  I looked at a smaller set of data and ran some tests on it.

 

The data I tested was the following 6 values - 45.18, 45.26, 45.21, 45.39, 45.24, 45.41

 

The sum value should be 271.69, however the function is returning a sum of 270.00 (which is each value rounded to its nearest whole number - 45*6)

 

I then changed the first value to 45.68, at which point the function returned a sum of 271.00 (rounding the first value up to 46, and continuing to round the remaining values down to 45)

 

Unfortunately I'm not in a position to attach any samples, but if there is any reason that you think this issue is occurring then please let me know.

 

Thanks in advance.

 

Micheal

Hi Michael,

 

Ok.  Can you please replace the previous UDF with this one attached below. This should fix the problem.

 

Please let me know how it goes.

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

 

Hi Jamil,

 

Thanks again for the response.

 

I've just tried your amended code and it works perfectly.  Your help on this is very much appreciated!

 

Best Regards,

Micheal

You are welcome. Thanks for the feedback
Hi Jamil,

I have a GANT diagram for were I want to sum the values in column cells with a specific colour.
This is to sum how many labour hours we have to make each day for a specific background color that was generated by the conditional formatting.

In this GANT diagram a pop up boxt can not be used. Do you have a solution for that?




Is it possible for cell count instead of sum. Please share the VBA code if possible.

Yes, it is possible and here it goes.

 

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

Hi t.aerdts,

 

I think that can be achieved using Formulas with the referred UDF. If you post a sample dummy data here and I will have a look in the weekend. 

 

Hi Jamil,

 

herewith the sample file.

 appreciate your input.

I put some comment in the file.

 

KR T.aerdts

 

 

 

Hi,

 

I just noticed that the zip file you uploaded is blank. 

 

besides, this thread is related to conditional formatting, so i suggest you open a new question with uploading the sample file with it.

Hi Jamil,

 

attached the file with content.

It would be great if the cells can be summed when the color is set by conditional formatting.

inside the excel file I insert some remarks and tekst for declaration.

 

Tillo 

Hi Tillo,

 

I looked at your file. you are using a incorrect UDF to sum cells based on conditional formatting.

 

I cannot fill your template for you.  you need to copy the below UDF put it in a module in your workbook.

 

then use it in your workbook =SumConditionColorCells(CellsRange As Range, ColorRng As Range)

 

like this =SumConditionColorCells(O$9:O$40,$N6)

 

I found another problem with your workbook. the color used in the conditional formatting and the cells referenced as the color criteria e.g. $N6 are not the same color.  unless they are the exact same color, the UDF will not work.   I have attached a workbook that shows how the UDF works. so you need to adapt that in your own workbook.

Sorry, I cannot help further on this. 

 

 

 

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

 

1 best response

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

Hi matt nipper,

 

since you mentioned that you "have done an exhaustive search online" i have come up with a solution for you, although it has two limitations A) it will only work, if your rules of conditional formatting is created using conditional formatting rules with formula aka (use a formula to determine which cells to format) and B) the UDF will only work if sum range is more than one cell another word, it will not sum a single cell, as well as the conditional formatted range is more than one cell.

 

the example file, you can download it from here. I could not upload it here, as it is a Excel Macro-enabled Workbook that contains the 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

 

 

Capture.PNG

in the attached example file you can see that from A3:G16 cells are formatted using Conditional Formatting.

User Defined Function (UDF) is placed in cells J2 & J3 and cells I2 and I3 are the criteria color used as reference inside the UDF in J2 and J3.

 

I hope this helps you.  

 

 Edit: uploaded file and updated the code to the correct one.

View solution in original post