Forum Discussion
matt nipper
Dec 20, 2016Copper Contributor
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.
- 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) - 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!
- 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.
- 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.
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.
- 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.
- TomNguyenvnCopper Contributor
Jamil Dear Sir,
I tried to use your code but seem it does not work for my file. Kindly help me to check and correct if available- JamilBronze Contributor
you conditional formatted applied ranges were not the same as the range used in the UDF. so i changed the range of the CF and it works. please see attached.
Deleted's suggestion of an extra helper column for a sumif is by far the safest and most easily understood option.
- 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.