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.
I looked at your file. Your range of CF do not match the range in the UDF. If you read my earlier posts, I have reiterated in several posts that the range in the CF should be consistent with the range used in the UDF. So, I changed the CF for that region to match with the range used in the UDF. Another issue that I found with your file is that you are also using the cell backgroud coloring in the same area where conditional formatting is used. you cannot use the UDF when you color the cell using both CF and manual changing of background coloring. you should set the CF in a way that there should not be a need for manual change of color. for example the range there already had a pre defined blue colors, so when CF criteria did not meet, the cell original pre defined color was visible and hence the UDF was returning incorrect result. I have removed the pre-defined cell colors and left only CF. I suggest you do not use the method of manual coloring as you have described in the criteria for blue cell. Perhaps, you are overkilling this with complicated solution while this can be done easily with built in formulas and CF alone. I have attached the file for your easy reference, try to examine the CF rules.
Everything is working great from all your help but I have one issue that keeps popping up. I have multiple columns that have independent CF that are summed by the CF per each column. I have different people assessing the file and updating it with dates. Randomly, or at least I see it as random, is that all the CF is combined across all the columns. All the CF works perfectly fine still but the CountConditionalCells UDF then only uses the first column and applies that summed values across all the columns. I then have to go into each column and reapply unique (but identical) CF. This is quite frustrating and I am sure it is something I am doing incorrectly. Any help would be greatly appreciated, I read through the earlier posts and could not find this failure mode discussed.
Thanks for your message and kind words.
I guess when you involve the manual background changing color to blue, then it will have to be the way that you have already adopted. I am sure there are better ways to make it robust, but this is as far as I could go. Thank you for your understanding.
You're 100% correct, adding them manually and they are time consuming!
A few issues - and I added a small sample excel sample of what I am talking about to make it easier to understand my issues.
1. When I remove a row from the sheet (and it is not at the bottom of all the rows) all the CF is combined across the columns and then negates the CountConditionalCells UDF.
Possible solution:
A. Add a "closed" note to the row but the file is SLOOOW as is based on all the CF this will just keep a lot of extra CF cells I don't need to track or care about.
B. Push the "to be deleted" row to the bottom and delete, but this is not robust and many teams are adopting this file and someone will mess that up.
2. When I add a row to the sheet it only has the CF for that specific row and does not automatically roll the CF into that row. Which is then another manual process to update the CF to capture that row even if with a paint brush method.
3. The entire sheet is based on dates that are auto populated based on previous up stream deliverables and standard lead times to complete the tasks. The only way I have found to mark a cell (deliverable) as complete (and to save the date for scheduling and historical purposes) is to have the team member go in and manually change the color to blue which triggers a CF based on that color to change the font white. So I am unable to do a paint brush option to all the columns even one at a time as there are some that have manually changed backgrounds because the deliverable was completed and the date stored for deriving the next step or historical purposes.
Again, you're the man with this stuff and all the help you have given so far has been amazing and I really appreciate it. Hope you and your family are doing well during the COVID-19 Impact and stay safe Jamil.
you are probably manually adding the conditional formatting on each column which can be time consuming. you can avoid that, by using the pain brush option of Excel.
select the single column that has the CF and then double click on the Excel paint brush and then now click each of the other columns that you want the CF applied. this way the exact CF will apply without the hassle of manual creation of CF.