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

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

@Jamil Mohammad 

 

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?

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!!
you are welcome cp411.

thanks for your feedback.

@Jamil Mohammad 

 

you look like the man. I'm trying to do something simular to what you have done here. 

 

I want the hyperlinked cells on the overview sheet to turn red if any cell on any of the hyperlinked sheet is red, and green if there isn't a red cell. there is a lot of conditional formatting going of for the cells so hard to to duplicate into the one cell.

 

I have tried GetColor on the the sheet but just cant get it to get a response, I have got the Bankhead hyperlink cell on Farm overview (sheet 1) to turn red by Bankhead (sheet 6) cell AP4 with conditional formatting, but just need a formula to get a response from the above criteria

 

I have now tried adding this VBA but that isn't working either

 

not sure if there is an easier way or some part of the code is wrong

@Ade_Salmon 

 

Thanks for your kind words.

 

I looked briefly at your file. Found couple of issues.

 

A) your conditional formatting are not consistent. For example Worksheet BankHead column O has 4 rules in conditional formatting. then your column Q only has 1 rule and column R has 3 rules.  so, there is no clear pattern for a formula to determine the count.  If your columns CF were having the same pattern of rules, then we could have used a simple formula in a helper cell for your overview sheet to turn red if any of those columns cells condition were met. For example this formula =SUMPRODUCT(--(C3:AL3<C1)*(LEN(C3:AL3)=5))-COUNTIFS(C3:AL3,"X")  it checks if the cells are date, it then checks if the dates are smaller than the today's date, then finally if they are done which has X then it will exclude it. this formula you could have used for red color, if your columns had consistent rules. but they haven't, so this formula is returning incorrect value. This formula gives you an idea how you can make it work, by fixing your CF rules.

 

B) you have used two many unnecessary If functions with ISblank function to evaluate if the cells are not empty. This has caused your workbook to become sluggish. instead of =IF(ISBLANK(A5),""   you could first evaluate that If cell is not empty then do that without the use of ISBLANK function. The way it is now, it first evaulates every cell if it blank or not.  so, you could use something like =IF(A5<>"",$A5-7*4,""). 

 

C) TODAY functun is volatile, using it multiple time in everysheet is slowing down your workbook.

instead you can have one single cell with TODAY function and all other formulas are referenced to this single cell. in fact you could have one single named range with =TODAY() function and it is used in your formulas across the sheets.

 

D) you have pre-populated 500 rows in each of the sheets with the unnecessary formulas. I understand that you need to have those formulas in case if the data grows, however you can avoid those 500 cells with pre populated formula by using Excel Tables feature, which has dynamic nature that when you add new data all formulas from the above cells are automatically copied down. so your workbook will work much faster and more dynamic with Excel Tables rather than these 3500 rows with formulas which sucks the CPU.

 

Hope it helps.

Jamil

that is brilliant I am only an amateur at this piecing together the bit of knowledge I know to make this for my wife, not used tables before but I will look into correcting the spreadsheet with you recommendations

thanks a lot

Ade

@Jamil Mohammad 

 

Hi Jamil,

 

done everything you suggested, it has worked a treat but still cant get the overview cells to change to red or green from their relevant sheet

 

hope for some assistance 

@Ade_Salmon 

 

Great!  now only little has remained.

 

Please see attached workbook. I have placed a formula next to the Bankhead icon in cell A5 of Overview sheet.   this is the formula =SUMPRODUCT(--(Bankhead!C3:AL3<Bankhead!C1)*(LEN(Bankhead!C3:AL3)=5))-COUNTIFS(Bankhead!C3:AL3,"X")  

 

then I used custom formatting and in custom formatting I put ;;; three semicolons, so that the value returned is not visible. Then I created a conditional formatting on cell B5 that If cell A5 is greater than zero then turn red.  

 

You can replicate this with the other worksheet icons as well.


Hope it helps.

Jamil

works a treat, you are a wizard

thanks a lot

@Jamil Mohammad  

 

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

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

@Jamil Mohammad 

 

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!

@Cristian1980 

 

You are welcome.  Thanks for feedback.

@Jamil Mohammad 

Thank you for this, i have been trying to trouble shoot my issues for the past week or so to no avail. The problem is I need to use quite a few different colors for conditional formatting. I have made sure that all the colors match in terms of RGB between the painted cells and the conditional cells. But after the first 9 (the 9/10 standard pallet colors) the math doesn't seem to be accurate any longer. Is there anyway you can take a look at my file and see if there is anything I can do to fix this or if you have any advice on this?

@Mitchell_Wade 

 

Hi,  perhaps you may have not read my earlier posts to similar issues.

 

Your conditional formatting range in the CF and inside UDF were not matching.

 

I change your CF applied ranges in a way to match the UDF and now it works, see attached.

@Jamil Mohammad 

Thanks for taking a look at it, but I am still seeing the errors even in the preview version you sent. The times in Column D do not sum to their corresponding colors in column H and Column M. This is the issue I am speak of. for example. if you look at D4 and H4 they are not the same at all. D4 is calculated to be 20 mins while H4 is only calculating 7 minutes.

@Mitchell_Wade 

 

it worked and i tested it.  try to remove the time format and change it to just normal general number format.  then add another acitivty in column A for example 15) and then add a value in column D and then it will see that UDF adds up that amount into corresponding color of the  15)

Hi @Jamil Mohammad, the sample file uploaded is still showing #Value error message.

I am getting a ZERO result when applied.

I have copied the CountConditionColorCells UDF and have conditional formatting on a row, which I am trying to count.

I am also using this in cell A6:  =CountConditionColorCells(C6:V6,A6)

Row C is the conditional formatting row. The CF is working correctly, but the UDF is not co-operating.

Any ideas?

 

This is the EDF I have copied:

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 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End Function

 

I would appreciate any help you can provide.

 

Thanks,

Van

@Jamil Mohammad 

Dear Sir, i have been trying to count the number of cells having two separate conditional formatted colours in a leave planer calendar. However after applying the VBA code that have been given by you i am getting the result as only 1 in my target cell. i have atfached the file, hoping for your help