Forum Discussion

matt nipper's avatar
matt nipper
Copper Contributor
Dec 20, 2016

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 color.  

 

I have done an exhaustive search online and was able to quickly find a way to sum by color, unfortunately it only worked for cells whose color was set manually (NOT using conditional formatting).  I have found a few references to VB code that should provide the functionality but I can't get any of them to run (except for the one that works for manually set colors).

 

I have control of the data that I'm trying to sum.  is there another method to "tag" values?  I thought of adding a letter prefix, but coulnd't find any way to sum a column of numbers that are contained in text strings.

 

Any help would be greatly appreciated!!

 

I'm using Office 365 (excel 2016) on a windows 10 machine

 

  • 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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    You cannot count colors assigned by conditional formatting, as these cells are effectively still the default background color!
    Why not just count these cells based on the conditions you used for conditional formatting?

    Example:
    Set in a worksheet with conditional formatting so that the cell background turns green when the cell contains the value 2. COUNTIF counts all cells that contain the value 2 = all cells whose cell background has turned green due to the conditional formatting

    Is simply a quick solution before you start with VBA code.

    Thank you for your patience and time.

    Nikolino
    I know I don't know anything (Socrates)
  • Cristian1980's avatar
    Cristian1980
    Copper Contributor

    Jamil  

     

    I've study this thread for few days already... I have a problem that I want to solve, in my example I was playing with a personal project, and basically what I want to do is after using conditional formatting, I'm using a match function, I'm highlighting a few numbers, what I want to finally obtain is the COUNT of each matching numbers on each row... what I have done is a double filtration based on a combo box which then print diferent lists and then match numbers based on CF.

    I like to say about myself that I'm proficient in using Excel, but I'm kinda stuck on this problem!

    I did try diferent approaches and examples that I found on this thread, but still didn't find the correct combination.... 

    • Jamil's avatar
      Jamil
      Bronze Contributor

      Cristian1980 

       

      You can use SUMPRODUCT with COUNTIFS.  COUNTIFS criteria can do function argument array operation when we place a range instead of single criteria and then we wrap it with SUMPRODUCT to eliminate the need of special keystroke.

       

      I placed =SUMPRODUCT(COUNTIFS(D3:O3,$D$1:$I$1)) in P3 and copied down.

       

      please see attached file with formula.

      • Cristian1980's avatar
        Cristian1980
        Copper Contributor

        Jamil 

         

        Thx! for the idea, I was kinda fixed on the counting using scripting, because the usual COUNTIFS from excel didn't give to much initially, now I understand that it did requiter an other combination; now I can continue my project. Thx!

  • Suril Soni's avatar
    Suril Soni
    Copper Contributor

    Hey Jamil, 

     

    I  used the count condition to calculate the cells based on conditional formatting and for some reason at first it showed No COUNT and now it shows value error. 

     

    I am attaching the file. Can you please look at it and provide your feedback on how to fix the error. 

     

    Note: - I changed the name from  .xlsm to .xls to upload it

    • Jamil's avatar
      Jamil
      Bronze Contributor

      Suril,

       

      you have used the UDF as COUNTIFs function which does not work.  It accept one range and one criteria, it does not work when multiple criteria and ranges are given in the UDF argument.

       

       

      • samaneeh's avatar
        samaneeh
        Copper Contributor

        Jamil 

        hi jamil

        I used your code but it does not work.  my excel is multiple criteria and ranges . is there any solution for my problem?

        thanks in advanse for your help

         

         

  • richard daniels's avatar
    richard daniels
    Copper Contributor
    SUMIFS and SUMIF I don't think work. The problem as far as I can see is that conditional formats are volatile and must calculate after all cells have calculated. Therefore you cannot have a function that will sum the displayformat color easily.
  • Hello,

     

    take a look at this question in the Microsoft Community formerly known as the "Answers" Q&A forum. (I know, the "Community" site name is confusing to have on two different sites).  

     

    Make sure to look at all the replies to see how to get from conditional formatting to a Sumifs or Countifs formula.

  • Jamil's avatar
    Jamil
    Bronze Contributor

    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.

    • LisaKer's avatar
      LisaKer
      Copper Contributor

      Jamil I am also getting the No Color error and desperately need your help.

      I have tried to troubleshoot but have not had any success.

       

      How can I attach the file for you to view?  It keeps telling me "file type is not supported"

    • ajt8888's avatar
      ajt8888
      Copper Contributor

      Hello Jamil I have looked at this and downloaded your spreadsheet but  the formula in J2 and J3 doesn't count anything it just says #Value! am I missing something here because it doesnt seem to work at all. Jamil 

      • Jamil's avatar
        Jamil
        Bronze Contributor

        TomNguyenvn 

         

        you 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.

  • Deleted's suggestion of an extra helper column for a sumif is by far the safest and most easily understood option.     

  • Hi Matt,

     

    may be using the SUMIF or SUMIFS functions could help. You can define as criteria the same criteria you defined for the conditional formats. And, you don't need macros then. If you prefer macros, please have a look on this article from Ablebits. There is a section for adding values on conditional formats.

     

    Best,

    Mourad

    • matt nipper's avatar
      matt nipper
      Copper Contributor

      Thank you for the response Mourad.  sorry i didn't see your post until Wyn responded.

       

      I will try the sumifs function. I hadn't thought about burying the conditional formatting formula in the sumifs formula.  Maybe that will work.

       

Resources