## Forum Discussion

# sum by color when colors are set by conditional formatting

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

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

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.

- JamilBronze Contributor
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

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.

- Micheal HillCopper Contributor
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

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

- Fenfen GaoCopper Contributor
- JamilBronze Contributor
Hi Fenfen,

you have given the range of cell into the UDF range arguments cells that do not have conditional formatting applied to them.

UDF will throw error if range of cell given as input that do not have conditional formatting rule applied to it.

so to make your workbook work. you could achieve the result by using a combined addition

=COUNTConditionColorCells(B3:I3,J2)+COUNTConditionColorCells(B8:I8,J2)+COUNTConditionColorCells(B13:I13,J2)++COUNTConditionColorCells(B18:I18,J2)

see attached file.

- Andreea ToporCopper Contributor
How to sum conditionally formatted cells based on adjacent cell

Hi Jamil,

I tried to use the UDF you shared to sum up conditionally formatted cells based on adjacent cell. The conditional formatting is done using SEARCH formula, basically I am trying to sum up all the sales values for which the adjacent cell (text) contains certain strings. I saw your post related to more complex formulas like VLOOKUP with which the UDF doesn't work, not sure whether SEARCH falls under the same. Appreciated if you can look into it and let me know in case something can be done so the UDF would work in my case as well. Attached the example file.

- JamilBronze Contributor
Andrea,

I think you can easily achieve the result you need without over-killing it with conditional formatting UDF.

in I5 put Worldwide and in I6 put USA and in the adjacent cell in J5 put this formula and copy down =SUMPRODUCT(--(ISNUMBER(SEARCH(I5,$E$5:$E$12))),$F$5:$F$12)

this will sum based on the condition given and the good part about this is that you do not need to save your excel file with macro enabled option. you can get your results by using built-in Excel functions which is great.

please see attached workbook where I embedded the formula in it.

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

- matt nipperCopper Contributor
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.

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

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.

- richard danielsCopper ContributorSUMIFS and SUMIF I don't think work. The problem as far as I can see is that conditional formats are volatile and must calculate after all cells have calculated. Therefore you cannot have a function that will sum the displayformat color easily.
- Suril SoniCopper Contributor
Hey Jamil,

I used the count condition to calculate the cells based on conditional formatting and for some reason at first it showed No COUNT and now it shows value error.

I am attaching the file. Can you please look at it and provide your feedback on how to fix the error.

Note: - I changed the name from .xlsm to .xls to upload it

- JamilBronze Contributor
Suril,

you have used the UDF as COUNTIFs function which does not work. It accept one range and one criteria, it does not work when multiple criteria and ranges are given in the UDF argument.

- Suril SoniCopper Contributor
Thank you Jamil,

Is there any solution for that?

Appreciate your help.

- Cristian1980Copper Contributor
I've study this thread for few days already... I have a problem that I want to solve, in my example I was playing with a personal project, and basically what I want to do is after using conditional formatting, I'm using a match function, I'm highlighting a few numbers, what I want to finally obtain is the COUNT of each matching numbers on each row... what I have done is a double filtration based on a combo box which then print diferent lists and then match numbers based on CF.

I like to say about myself that I'm proficient in using Excel, but I'm kinda stuck on this problem!

I did try diferent approaches and examples that I found on this thread, but still didn't find the correct combination....

- JamilBronze Contributor
You can use SUMPRODUCT with COUNTIFS. COUNTIFS criteria can do function argument array operation when we place a range instead of single criteria and then we wrap it with SUMPRODUCT to eliminate the need of special keystroke.

I placed =SUMPRODUCT(COUNTIFS(D3:O3,$D$1:$I$1)) in P3 and copied down.

please see attached file with formula.

- Cristian1980Copper Contributor
Thx! for the idea, I was kinda fixed on the counting using scripting, because the usual COUNTIFS from excel didn't give to much initially, now I understand that it did requiter an other combination; now I can continue my project. Thx!

- NikolinoDEGold ContributorYou cannot count colors assigned by conditional formatting, as these cells are effectively still the default background color!

Why not just count these cells based on the conditions you used for conditional formatting?

Example:

Set in a worksheet with conditional formatting so that the cell background turns green when the cell contains the value 2. COUNTIF counts all cells that contain the value 2 = all cells whose cell background has turned green due to the conditional formatting

Is simply a quick solution before you start with VBA code.

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)