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 Jamil,
I have a large database (In excess of 200Columns & 8000Rows) which needs to be sorted out by cell color and other multiple criteria. I have tried out multiple things but failed.
I'm basically looking for type of SUMIFS function with ColorIndex as Criteria.
A snap shot is attached.
it does not work because you perhaps did not read the rule on how to use this UDF. please see my post here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditional-formatting/m-p/40312/highlight/true#M28
""UDF 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) ""
you did not attach anything, besides your question is not relevant to this thread. try posting a new question,
aditya wrote:
Hi jamil,
I'm little lost & confused and trying to make my code bit short.
In multiple if, Elseifs statements in VBA, I need to use array, But not able to debug it.
Excel version of same is attached, Can you help me with vba code..
Hi jamil,
I'm little lost & confused and trying to make my code bit short.
In multiple if, Elseifs statements in VBA, I need to use array, But not able to debug it.
Excel version of same is attached, Can you help me with vba code..
- You are welcome. glad it worked for you.
Hi Jamil,
Your suggestion worked perfectly.. Thanks at lot..
- Hi Jamil,
Actual what you are same is Correct, I'm already running VBA code in 10 parts to save CPU Memory.
The second logic with example makes some sense, I will try the same in greater depth of details.
I will let you know the result. Thanks in Advance. Hi null null,
what you are trying to achieve can be done with a very complicated UDF but will be very CPU hungry.
If your colors marks are based on some hardcorded condition which is not from conditional formatting, then use those conditions inside the SUMIFs or SUMPRODUCT and if your colors are based on conditional formatting rules, then use those rules you used in the conditional formatting inside the SUMIFs or SUMPRODUCT formula.
for example, from your previous file, you have condition of if cell values is between
=0.2791 and =0.3062 then it is red.
so you can use the same logic inside the formula for example like this =-SUMIFS(E3:K35,E3:K35,">=0.2791",E3:K35,"<=0.3062")
Hi Jamil,
The said UDF is not suitable to my problem as it cannot sum/count with multiple criteria.
Further i have checked multiple forums for sumif by Cell Color but non are matching my problem.
Extended version of sample result required is attached.
Hi Jamil,
I had read the all the thread post, But the problem is my worksheet is too large to apply conditional formatting and excel crashes upon conditional formatting.
Further, Color coding as seen in each cell is coming from series of various conditions in other spreadsheet. (i.e For each cell in Qty, There are 4-5 activities to be completed each activity having unique color, If particular activity is completed, It will return only "ColorIndex" in current sheet)
Hence, I need to sum the fields using "ColorIndex" and other criteria's.
