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 have a GANT diagram for were I want to sum the values in column cells with a specific colour.
This is to sum how many labour hours we have to make each day for a specific background color that was generated by the conditional formatting.
In this GANT diagram a pop up boxt can not be used. Do you have a solution for that?
Hi t.aerdts,
I think that can be achieved using Formulas with the referred UDF. If you post a sample dummy data here and I will have a look in the weekend.
Hi Tillo,
I looked at your file. you are using a incorrect UDF to sum cells based on conditional formatting.
I cannot fill your template for you. you need to copy the below UDF put it in a module in your workbook.
then use it in your workbook =SumConditionColorCells(CellsRange As Range, ColorRng As Range)
like this =SumConditionColorCells(O$9:O$40,$N6)
I found another problem with your workbook. the color used in the conditional formatting and the cells referenced as the color criteria e.g. $N6 are not the same color. unless they are the exact same color, the UDF will not work. I have attached a workbook that shows how the UDF works. so you need to adapt that in your own workbook.
Sorry, I cannot help further on this.
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
Hi Jamil,
attached the file with content.
It would be great if the cells can be summed when the color is set by conditional formatting.
inside the excel file I insert some remarks and tekst for declaration.
Tillo
Hi,
I just noticed that the zip file you uploaded is blank.
besides, this thread is related to conditional formatting, so i suggest you open a new question with uploading the sample file with it.
Hi Jamil,
herewith the sample file.
appreciate your input.
I put some comment in the file.
KR T.aerdts
