SOLVED

sum by color when colors are set by conditional formatting

Copper Contributor

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

 

304 Replies
Thank you very much!

Hi @Jamil Mohammad, firstly thanks for taking the time to help and reply to so many members. 

I have read through the whole thread and cannot find the solution to my issue.  Hope that you are able to help.

 

I am trying to use your count UDF to count the number of cells conditionally formatted either red/orange/green/white as per the bottom of the table.  My CF are all defined by a function, and ranges seem to match up, colours are the same as I have inputted the RGB values.  I am still getting the #VALUE error in rows 136 through 138, and a 0 count in row 139.  Maybe my CF rules are too complex?

 

Thanks

Aidan

Hi  ASharp

 

Thanks for your kind words.

 

I looked at your file. the CFs are not consistent with the range used in the UDF.

 

for example the red color range in CF is =$AP$135,$H$4:$BK$134   double while in the UDF it is only H$4:$H$134    if you read my previous messages, you will see that most of user miss this part. 

I noticed that you you CF formulas are also having circular reference issue. 

You can simplify your workbook by using helper column/cells and perhaps a formula solution to count is much more easier than the use of this UDF.  I have given several examples in the earlier comments on this thread to other users.

 

Hope it helps.

 

@Jamil Mohammad

 

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

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.

I am trying to use your formula, and I keep getting zero count.  Here is my file.  I'm hoping that you can help me.

it is because the formula logic used in the CF are conflicting and are not correct.

for example, your Math1 color count is 63 while your formula is =COUNTIF($F:$F, $E2) and it highlights 112 however the reason you do not see the remaining colors because they are recolored by other CR rules that takes precedent. so to make this work, you should properly set your CF formulas.

@Jamil Mohammad 

 

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!

@soophie6 

 

It should work. Can you upload a sample file?

 

 

@Jamil Mohammad 

 

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!

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.

@Jamil Mohammad 

I want to count the number of yellow boxes as in the file below and output in the column "count color yellow"
Thank you so much!

@Jamil Mohammad 

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

Hi Jamil!@Jamil Mohammad 

 

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

@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.
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.
MrsLSkinner

paint brush should work. can you upload a dummy sample file?

@Jamil Mohammad 

 

@Jamil Mohammad 

 

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!

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

@Jamil Mohammad 

 

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?