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.

Arup Kumar Das's avatar
Arup Kumar Das
Copper Contributor
Feb 25, 2018

Is it possible for cell count instead of sum. Please share the VBA code if possible.

Jamil's avatar
Jamil
Bronze Contributor
Feb 26, 2018

Yes, it is possible and here it goes.

 

Function COUNTConditionColorCells(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 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function
  • hitmand's avatar
    hitmand
    Copper Contributor
    Feb 02, 2022
    Hi Jamil
    First of all, thank you for this code it has been massively helpful to me. I have an issue I am hoping that you can help me to resolve. I got the code working except for this part:

    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))

    My understanding is here is where the code is counting the change in colour and updating the 'dbw' variable accordingly. I have used your example version and it works perfectly. When I run it in my workbook it doesn't work once the loop is finished I am always returned 0. If I edit the code I can get it to count all the cells that have a colour but I then have the issue that it won't differentiate between the different colours and will return me the number of all the coloured cells. Please, can you advise why this section of the code may not be working for me?
  • Sukesh_renake's avatar
    Sukesh_renake
    Copper Contributor
    Aug 02, 2021
    Hi Jamil
    Thanks for the solution, is there a limit in setting a range because when i am trying to set range with more than 7 columns its saying #value.

    can you please help
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Jan 17, 2021

    cvgme 

     

    Hi,  it is because inside the UDF you are using absolute column reference and when you copy it to the right, the same reference copies and that is why you get the same result. using F4 key, try to change the column reference to relative reference.

  • cvgme's avatar
    cvgme
    Copper Contributor
    Dec 09, 2020

    Jamil Hi Jamil, thank you for this wonderful formula, it's my first UDF and was very easy to set up. I have the same issue as others regarding the fact that the formula only works for the first column and then every column after is returning the same count as my first column - however this link you're referencing appears to be dead. I'm hoping you can help me by resharing the link to how to resolve this issue and/or video. Here's the formula returning the same answer for every section (I counted and it should not be 9 for every single month). Thank you!

     

  • Jamil's avatar
    Jamil
    Bronze Contributor
    May 06, 2020

    Z Z 

     

    Hi,  albeit your question is not related to the topic in this thread, I will try to give you an answer.

     

    there is an easy way to check if a range is sorted sorted.  It is quite easy, even if you have a single blank in your data.  Examples https://exceloffthegrid.com/excel-formula-to-check-if-a-list-is-sorted/ 

     

    However, with the data you presented, it is much more complicated as the data is scattered with multiple blanks in between them and sometimes there is only a single value or duplicate values which makes it difficult to find a pattern for the formula. 

     

    Perhaps you can work out using some helper columns to clean the data before running the logical test. 

     

     

  • Z Z's avatar
    Z Z
    Copper Contributor
    May 05, 2020

    Jamil 

     

    Jamil,

     

    Thanks for helping, and sometimes doing it almost at light speed.  You have helped me in the past.

     

    I have a similar problem, but instead of counting, I just need to show on the screen by conditional formatting which numbers match the criteria.

     

    How can I use conditional formatting to highlight all the numbers in a row if they go in ascending order across columns, and skipping the blank cells.

     

    Also, which formula do I use to show next to the beginning (or end, or someplace) of the row a "YES" if the criteria is met?

     

    I attached an example. I repeated the tables. The fist table at the top is where the CF or formula is needed. The bottom part, the repetition is where I illustrate what I need, how it needs to look.

     

    Thank you so much for helping.

  • khaled170's avatar
    khaled170
    Copper Contributor
    Apr 16, 2020

    Jamil First thank you for this effort. Second I try the Function you gave to count the formatted colored cells, it is working only for one row and I need to pull down it always giving the reading of first row. Please Can you help with this to let this Function working as I pull down to the next rows.

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 29, 2019
    you are welcome cp411.

    thanks for your feedback.
  • cp411's avatar
    cp411
    Copper Contributor
    Apr 26, 2019
    Actually figured it out! I have to have the cell in the first list match the header in the block of other cells. Thank you so much for your help!!
  • cp411's avatar
    cp411
    Copper Contributor
    Apr 26, 2019

    Jamil 

     

    Thank you! That worked great! I have one more question and hopefully I'll be set. I've tried changing the name of Project 1, Project 2, etc., but when I do the entire corresponding row is filled with the color. How can I change the text in column A without the row filling?

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 26, 2019

    cp411 

     

    you can simply use =COUNTIF($C3:$ND3,1)  in B3 and copy down.

     

    Please see attached.

     

    I also removed the UDFs from your file and saved it as normal xlsx file. as you no longer need to xlsm format.

  • cp411's avatar
    cp411
    Copper Contributor
    Apr 25, 2019

    Jamil 

     

    Thank you so much for your quick reply! I've reviewed the file but I still have one question. How can I count all the filled cells in a row and tally them in the "Total Days" column? For example, in row 3, I'd like to keep a running total under "Total Days" of all the filled cells in that row. Is there a way to do that?

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 25, 2019

    cp411 

     

    Hi cp411,

     

    Thanks for your kind words.

     

    I looked at your file.  You do not need to overkill it with UDF and too many repetitive conditional formatting rules.

     

    You just need a simple formula like this one, I wrote for you.

     

    First, I deleted all of the conditional formatting rules that had the pattern style in the range area C3:ND16.

     

    Then I placed one single formula =IF(COUNTA(OFFSET(INDEX(C$18:C$114,MATCH($A3,$A$18:$A$114,0)),1,0,5,1))>0,1,0)

    and copied down and right in all range up to C3:ND16.    

     

    then, I selected area C3:ND16  and right clicked  and then clicked on "Format cells" and then "custom format" then used three semicolons as the custom format  like this ;;;

     

    click ok and it has hidden the content of the cells from visibility. 

     

    then I selected C3:ND16 range and created one single Conditional formatting rule =C3=1 and selected fill pattern style and clicked ok.

     

    Saved it.   and uploaded it also here.

     

    you will see that this solution is much more simplified and robust.

     

    please see attached workbook.

  • cp411's avatar
    cp411
    Copper Contributor
    Apr 25, 2019

    Jamil 

     

    Jamil 

     

    Hi Jamil-

    Apologies in advance if this inquiry  is in the wrong place or if you've answered this particular question before but I am having an issue that I am hoping you can help with. I have tried using your UDF and it works, but it doesn't appear to be exactly what I need. Currently, my sheet is set up in a way that if any cell in a column is filled in a certain range then another cell outside of that range will be filled with a color. I would like to count the cell that is filled, not the cell that has the info inputted. Does that make sense? I've attached my spreadsheet.

     

    You have been so helpful with everyone on this thread and I hope you can assist me as well.

     

    Thank you!

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 12, 2019
    MrsLSkinner

    paint brush should work. can you upload a dummy sample file?
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 12, 2019
    hiepkt12

    this is not the sub routine i wrote. and it will not work for this purpose. you may need to use the function which I wrote to make it work. so, i cannot change that sub to function to make it work.
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 12, 2019
    yi513

    your formula to evaluate a condition is not correct. you cannot have multiple evaluation in the single condition. for example =IF(N$5<$L6, IF(N$5>=$K6, TRUE, FALSE), FALSE) is not correct. try to see how you can first put correct condition into the workbook. Also, please read my earlier posts on the limitation of UDF and also the troubleshootings of other users with similar problems.
  • MrsLSkinner's avatar
    MrsLSkinner
    Copper Contributor
    Apr 11, 2019

    Hi Jamil!Jamil 

     

    This is my conditional formatt rule for January, person 1:

    (Person 1, ROW 7) =AND(C9="EL",C9<>"") ------- Applies to =$C$7:$Y$7

     

    When I click the paint brush and paste to other rowns, the formula doesnt change, it just adds to the applies to box like the below:

    (Person 2, ROW 11) =AND(C9="EL",C9<>"") --------Applies to =$C$7:$Y$7, $C$11:$Y$11

    (Person 3, ROW 15) =AND(C9="EL",C9<>"") ------ Applies to =$C$7:$Y$7, $C$11:$Y$11, $C$15:$Y$15

    And so on...

     

    I need my conditional formatting to be:

    (Person 2, ROW 11) =AND(C11="EL",C11<>"") --------Applies to =$C$11:$Y$11

     

     

    This is my formula to average the coloured green cells with time values:

    =SumConditionColorCells(C7:Y7,JD4)/COUNTConditionColorCells(C7:Y7,JD4)

     

    I have already completed 1 full row for 12 months for 1 person, this has taken me all night!! Are you able to advise why my rule is not following sequence when copying using the paintbrush as per the video you posted?

     

    I appreciate your help with this, I am stuck in glue with this and I dont fancy completeing conditional formatting for 740 rows!!

     

    X

  • hiepkt12's avatar
    hiepkt12
    Copper Contributor
    Apr 10, 2019

    Jamil 

    I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.

    Help me!!

    Thank you very much!!

     

    Sub DisplayFormatCount()
        'Updateby20150305
        Dim Rng As Range
        Dim CountRange As Range
        Dim ColorRange As Range
        Dim xBackColor As Long
        Dim xFontColor As Long
        On Error Resume Next
        xTitleId       = "KutoolsforExcel"
        Set CountRange = Application.Selection
        Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type: = 8)
        Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type: = 8)
        Set ColorRange = ColorRange.Range("A1")
        xReturn        = 0
        For Each Rng In CountRange
            qqq           = Rng.Value
            xxx           = Rng.DisplayFormat.Interior.Color
            If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then
                xBackColor   = xBackColor + 1
            End If
            If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then
                xFontColor = xFontColor + 1
            End If
        Next
        MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor
    End Sub
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Mar 29, 2019
    Hi yi513

    It is because the range you used in the UDF and the range set in CF are not matching.

    example Range used for column N is N$6:N$205 while CF set is =$N$6:$BJ$205

    plus the the condition of UDF color is in C3 where none of the colors in the range $N$6:$BJ$205 matches that color.

    please read my earlier post where I have troubleshooted other users similar problems and that will give you guide on how to solve the issue on your workbook.
  • yi513's avatar
    yi513
    Copper Contributor
    Mar 26, 2019

    Jamil 

     

    Hi Jamil,

     

    I tried your code but it returns "0"s. Could you be so kind and take a look at my sample file?

     

    Thank you!

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Mar 22, 2019

    soophie6 

     

    It should work. Can you upload a sample file?

     

     

  • soophie6's avatar
    soophie6
    Copper Contributor
    Mar 22, 2019

    Jamil 

     

    Hi Jamil,

     

    Thanks for all your good help here. I copied your COUNT VBA but there is problem hope you can help me with:

    if the conditional formatting is done by simple formula like >50 e.g. it works.

    But my conditional formatting is done with this formula: =AND(T3<=$K3,T3>=($K3-$L3+1)).

     

    Now it doesn't work anymore. 

    Can you help out?

     

    Thanks a million!

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Feb 13, 2019
    Hi Julius835,

    As you can see in my earlier post. The range inside UDF and the range of CF both must be the same, otherwise the UDF does not count. Perhaps It would be easier if you use SUMPRODUCT function to count conditionally based on multiple criteria.

    Put a helper column next to column E. you can hide that if you want. put real dates in them and then use something like =SUMPRODUCT(--(MONTH(F:F)=MONTH(TODAY())),--(YEAR(F:F)=YEAR(TODAY())))

Resources