Forum Discussion

matt nipper's avatar
matt nipper
Copper Contributor
Dec 20, 2016
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...
Macros & VBA
Need Help
  • Jamil's avatar
    Jamil
    Jan 19, 2017

    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.

Arturo89's avatar
Arturo89
Copper Contributor
Feb 24, 2019

Jamil

 

Thank you for all that you have done in this thread. I've read through it all. Although some had similar issues, so far I haven't read one that's similar to mine. 

 

Here's what I need... For each row, there are 27 cells with conditional formatting. Based on the conditional formatting (using formulas to determine the format), the cell will either turn (standard) green, yellow or red. At the very end up each row, I have three more columns: Required, Completed, % Completed. I already have the formulas for the Required and Percentage columns figured out. Now, I simply need the formula to count all cells that have the standard green fill for the Completed column.  I tried what you have kindly offered on this thread, but it's not working for me. The two problems I'm facing are below.

 

Problem #1 - The UDF/formula does not count after 4 cells. 
I don't know why this happens, but the result returns as #VALUE! if I try to do all 27 cells in a row, but once you edit the range down to four cells or less, it seems to work. 

Problem #2 - The UDF/Formula seems to count all cells, instead of a specific color.
Along with problem #1, the formula seems to calculate any cell with any background color.
Let's take the following as an example: Formula =COUNTConditionColorCells(A2:C2,$E$2)
Cell E2 has a Standard Green fill. Cells in A2:C2 all have the background color of Standard Green, so the result is 3. Let's say I drag the formula down to row 3 now, which consists of all Standard Yellow. The formula *should* only count the cells with Standard Green fill ($E$2), right? In my case, it's still returning as 3.

Based off what I read in this thread, I imagine it something having to do with the conditional formatting being applied to nonconsecutive groups of cells. So, I went ahead and edited the document so I can apply each rule to one, consecutive group of cells. Unfortunately, this does not fix the problem.

There's really only one more thing that I can think of; however, I don't know if that's the main issue. There are some cells that do not contain a formula for conditional formatting. If you look at the spreadsheet, some of the cells have "NR" instead of a date. So the conditional formatting is set to "if contains." If this is the issue... what kind of formula would work to conditionally format a cell if it contains "NR"? 

I've attached a dummy file of my original one. I would appreciate your help very much! I would like the formula to be applied to AH9:AH80.

Arturo

Jamil's avatar
Jamil
Bronze Contributor
Feb 25, 2019

Hi  Arturo89

 

I looked at your file.

 

There was a lot of issues in the CF range , CF formula and the range.

 

I have demonstrated in the attached workbook how the CF are now calculating correctly.

 

things i changed in the file are the followings

 

Removed the CFs that was not set using formulas.

removed CFs with the whole range instead of per row

added the AND operator to only apply if cell is not blank.

applied the CF on row F3 to AF3 and then used Paint brush to duplicate it for all other rows.

your Range contained mixed input numbers and text. so i replaced the NRs with blank

 

please see attached workbook.

Resources