Forum Discussion
Solved
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...
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.
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 availableyou 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.
Jamil Hi Jamil, i've tried the vba you provided to sum colored cells from conditional formatting. But it doesnt work. Could you take a look my worksheet to see where the code went wrong??
Thank You.
Hi Jamil. Thank you so much for this thread. I don't know if it's still active, but I'm hoping that you'll be able to help me.
I tried using your attached example file and added a third color, which doesn't work for me. I did conditional formatting using formulas, but with an AND statement. Can you have a look at my file and tell me if I made an error, or if the VBA code doesn't allow for "more advanced" formula statements?
BR Esben
The UDF did what it supposed to do. It did not work in the example you shared because when you set the third condition in red color=AND(A3>50,A3<60) this was already overlapping with the green condition, meaning that green was already counting anything above 50 . Therefore, you had a wrong count on the green when I changed the green to >=60 then it worked. see in the attached example.
Hi Jamil
Thank you for the quick reply! Something seems to by off with my computer/excel.
When I first open the example file you sent, I am able to see the formula working as intended. However, after enabling editing, the formula stops working and gives me the "#VALUE!" error message - as shown in the second picture. Do you have an explanation for this? Could it be that I'm using excel in a different language? (Danish)
BR Esben
Jamil Hello Jamil!
I am trying to get the SUMBYCOLOR function working with conditional formatting but I don't know why it's not working. I took exemple with the file you provided in the first page, copied the VBA module into my file etc.. but I'm getting 0 as result 😞
Could you please help me out with this ?
I attached my file so you can see. Many thanks for your help!!
Hi, I looked at your file. the reason it did not work was because you had set the conditional formatting wrong. you had two overlapping conditions. you had =C4=$A$3 and you had another one =C4>$A$3
. I combined both of them as =C4>=$A$3 and then the UDF returned correct value. Plz check the attached file.
- WoW thanks a lot!! You rock
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 Jamil,
Your help with every one on this subject has been a great help! I have read this post and haven't been able to find anything to help me with the problem that I'm running in to. For some reason the totals by color are not correct. In the attached excel sheet; in Column F, I've set up a Conditional Formatting so that if Column E is greater than 1 but less than 200 it color fills to the light blue, for each row and set up different Conditional Formatting. Then I have the formula SumConditionColorCells on F25 for the total. The values of the light blue cells is really $327.60 but the =SumConditionColorCells is totaling it at $437.28. I saw a post on a rounding issue, I used that code and the original code that was posted and still getting the same value.
Any way that you could take a look?
Thanks!
Hi, It did not work, because if you read my earlier posts, I mentioned that UDF only works if your CF range and UDF input range are the same. in your file, they were not the same.
you can use built-in function to acheive the same result. see the formula in the attached workbook.
=SUMPRODUCT(F4:F23,(E4:E23>=1)*(E4:E23<=200))
Dear Sir, i have been trying to count the number of cells having two separate conditional formatted colours in a leave planer calendar. However after applying the VBA code that have been given by you i am getting the result as only 1 in my target cell. i have atfached the file, hoping for your help
Dear Sir, i have been trying to count the number of cells having two separate conditional formatted colours in a leave planer calendar. However after applying the VBA code that have been given by you i am getting the result as only 1 in my target cell. i have atfached the file, hoping for your help
The range you used in your UDF and the the range of conditional formatted cells are not matching, that is why it do not work.
please read my earlier post for other users with similar issues.
Hi Jamil, the sample file uploaded is still showing #Value error message.
I am getting a ZERO result when applied.
I have copied the CountConditionColorCells UDF and have conditional formatting on a row, which I am trying to count.
I am also using this in cell A6: =CountConditionColorCells(C6:V6,A6)
Row C is the conditional formatting row. The CF is working correctly, but the UDF is not co-operating.
Any ideas?
This is the EDF I have copied:
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 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End FunctionI would appreciate any help you can provide.
Thanks,
Van
