Forum Discussion
sum by color when colors are set by conditional formatting
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.
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.
I have tried working with your formula on a dataset, I want to count the formatted cells within one collumn. However the formula keeps returning an error: "#VALUE!" or keeps returning the value 0.
I think I have correctly added the macro. Also I checked the colourvalues, but they seem to be the same..
Do you have any suggestions for solving this issue?
Thanks in advance!
I looked at your file. the reason you are getting error, because you did not set the conditional formatting rules using formula. if you read my earlier posts in this same thread you will see that I have mentioned that for the UDF to work, the conditional formatting needs to be set using formula.
for example, I have changed the yellow part of the conditional formatting in your file and the UDF worked. Please see attached file. you need to change the other conditional formatting the way I changed it and they will work.
Hello Jamil,
I see you have been working with everyone on this great code for a couple years now - thanks!
I have a spreadsheet that tracks many different changes with many different functional groups. Each cell is a task to complete and I use your code to sum up the open, late, or closed items by each group I manage via the background color.
The cells have these color calls outs when applied to the CF I have prescribed:
1) Grey (when identified as grey using a different code); 2) Yellow (=""); Green (>today()); Red (<= today()); and finally; Blue for complete (manual change in background color with the conditional formatting to change the text to white). If a date is missed the team member presses Alt + Enter and places the next target date in which that can get done.
My issue is that when items are going from Green to Blue the counter now counts the one cell as both Blue and Green. Also, when a team member places another date in the cell after it is late the CF changes it to Red based on character count but the counter counts both it as Red and Green. Not sure what is happening, appreciate any help Jamil. I have attached a small sample set of data that I am vetting out before applying to the much larger tracker.
Thanks!!
Hi Johnny,
It was easy to spot what was causing the additional count. It is the UDF "IdentifyColor" you are using inside the Conditional Formatting. Therefore the UDF COUNTConditionColorCells counts them, because blue color criteria overlaps with the green criteria. for example. Green color condition is that if Date is greater than today. Hence, there are three dates that are greater than today in the list and hence UDF returns 3. disregarding the fact that there is another overlapping coloring applied by CF I=IdentifyColor(A1)="002060"
the UDF COUNTConditionColorCells works based on the CF when returns True, if there is a overlap, it does not look at the color, it looks at the condition you applied for specific color.
The solution for the overlap of blue is to simply subtract the value of blue from the count of green as shown in the attached workbook.
In regard to the green and red. there is no overlap, so it works. the green and red are not double counted. Please see attached file.
Jamil, you seem to have a really good handle on this, so I was wondering if you could look at my attached file and help me out.
As you can see, column AY displays sum from columns D through J. I've set conditional formatting to in AY depending on what is displayed in column B. Just as a test, I put dummy data in column D (COOP/COG at 1, DMP at 2, LEOP at 3, etc...). The conditional formatting works perfectly in column AY.
I then tried to use your formula "SumConditionColorCells" in column Q, expecting to see it going in order (COOP/COG 1, DMP 2, LEOP 3 etc...). For some reason DMP is displaying 1 instead of 2, down the list, Conduct is showing 3, File Management 8, etc....)
Do you know why it's not working? Thanks in advance for any insight you can provide.
Not sure if I understood the problem. as you are saying that DMP should show 2 instead of 1. however, there is only one color assigned for DMP and in the list of AY column only one time the DMP color exist. that is why it shows 1.
the reason for "Conduct" it shows 8 because you are using the SUM version of the UDF. so there is a cell with color associated to the "Conduct" which happens to have the value of 8 and therefore UDF correctly returns that value. If you want to count instead of SUM. then attached is the version to count. but since all of the cells in AY has only single color, the count returns 1.
please see attached.
