sum by color when colors are set by conditional formatting

[matt nipper]

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

[#2]

@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

[#3]

@santoshmahapatra 

The range you used in your UDF and the the range of conditional formatted cells are not matching, that is why it do not work.

please read my earlier post for other users with similar issues.

[#4]

@_-VAN-_ 

it is difficult to tell what could cause the return of zero. If you upload a dummy sample, I can look.

[#5]

@Jamil Mohammad 

Hello Mr. Mohammad,

Thank you for sharing your expertise about VBA Code using Conditional Formatting. I am not knowledgeable about the VBA Codes and Conditional Formatting Statements. Sir, I have made this worksheet after searching for VBA Codes online and applying them as per my requirements.  I  make a rather large worksheet (Range A1 : AK3000) every month wherein lot of calculations are there in Column Range J1:AJ3000. I have used colors for fonts in some cells and then used VBA code "SumByColor" (You can see the VBA Code in Module 4) to get the totals in the relevant cells at the end of the sheet. Sir I am attaching .xlsb sample file named "Master Worksheet" for your reference. I need help regarding Conditional Formatting and then using VBA Code for summing up the cells based on font colors.

 My reference in the worksheet here is specially for cells U2, V2, W2, X2 and then with reference to these cells for cells AG3, AH3 and AI3. Currently the sum of cell range W2:W17 is done by "SumByColor" VBA Code.

The color of cells W2, W4, W6, W12 and W15 is based upon the value of Tax Percentage in cells X2, X4, X6, X12 and X15 -for which I am using an IF Statement (=IF(V2<=2499.49,"12%","18%"),

 The value of X2 changes to 12% if the room tariff is:

• Less than or equal to 2499.49 : 12 %

The value of X2 changes to 18% if the room tariff is:

• Greater than or equal to 2499.5: 18 %

At present I am changing the color of the cells manually which is becoming a very tedious task. I have set the basic font color of all the cells W2, W4, W6, W12 and W15 as “Blue” and then if the value of cell X2 is 12%, I manually set the font color of cell W2 to Red.

The total of cell range W2:W17 gives the result in cell H18 (Total Basic Amount Room Revenue (Base Tariff for 12% GST)  for all the cells with Red Color Font and The total of cell range W2:W17 gives the result in cell H21 for all the cells with Blue Color Font (Total Basic Amount Room Revenue (Base Tariff for 18% GST).

Sir, I tried using conditional formatting to do the above as you have explained with examples, but when I use the “SumConditionColorCells” all I am getting is #NAME? or NO COLOR. Sir please tell me how to do it correctly so that I can get the sum of all the cells with Red Color Font in respective cell and also the sum of all the cells with Blue Color Font in the respective cell.

Similarly, the color of cells AG3, AH3 and AI3 is dependent on the value of cells X2 and the color of cells AG4, AH4 and AI4 is dependent on the value of cell X4. Kindly help me with the conditional formatting formula and the respective VBA code for these cells too.

The font color of cells AG3 and AH3 changes to Red, Accent 2, Darker 50% if the value of X2 is 12% and the font color of AG3 and AH3 changes to Red if the value of X2 is 12%.

The color of cells AG3 and AH3 changes to “RGB(217,151,149)” if the value of X2 is 18% and the color of cells AG3 and AH3 changes to Blue Color Font if the value of X2 is 18%.

The sum total of Cell AG2:AG17 goes to output cell “H32” if the value is for 18% Tax (Blue) and goes to output cell”H34” if the value is for 12% Tax (Red)

The sum total of Cell AH2:AH17 goes to output cell “H33” if the value is for 18% Tax (Blue)  and goes to output cell”H35” if the value is for 12% Tax (Red)

The sum total of Cell AI2:AI17 goes to output cell “H36” if the value is for 18% Tax (Blue)  and goes to output cell”H37” if the value is for 12% Tax (Red)

Sir I hope you have understood my problem and you shall help me by giving me the conditional formatting formulae for the cells and as well as a VBA Code to execute the command. Thanking you in anticipation.

[#6]

@Jamil Mohammad 

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

[#7]

@Pankaj_HSR 

Hi,

I opened your file. 

the reason you get #NAME error is becuase you have not used UDF which i wrote.  you have used another UDF which works only for background color and not for conditional formatting.

to make it work, you need to set conditionally format cells and then place the UDF in the VBA module and then it will work. 

I can help with troubleshooting the file, however, I am sorry I cannot build the workbook, as i do not have time.

[#8]

@samaneeh 

I cannot tell what would be the issue, unless i see a dummy example file.

[#9]

@Jamil Mohammad

tanks your answer.. it is my file

[#10]

@Jamil Mohammad Thank you for keeping this alive for so long.  I have attached a recent project to count cells and am running into an issue with the formula not updating until it is either clicked on or it is behind cell updates by 2 inputs.  What am I doing wrong?  Changing the "daily usage" on the attached sheet should give you the same result I am seeing.  

TIA

[#11]

Hi Mike,

the UDF works as it should.  your conditional formatting in the cell H2 is that if the cell value is smaller than 5 then turn it red and that is how it now count one because the color of that cell is red.  and in cell M3 color is yellow and in your range there is no cell with yellow color, so UDF returns zero.

[#12]

hi samaneeh


if you read my earlier posts, you will see that I have mentioned to other users the same thing that "(you conditional formatting applied range) and (range used in the UDF) both should be the same, otherwise it would not work. Try to do that and it will work.

[#13]

@Jamil Mohammad  thank you for your reply.  I understand but the calculation does not change as the data is updated.  It requires a "got focus"/double click to get the sheet to update after a change.  See the attached example, the field color can be updated but the count is always one update behind.  In the attached you will see the cell count for yellow is 0 but should be one.  Save the file and reopen or double click the formula field and it updates?  This always leaves my count off by 1 change.  Any advise?

[#14]

@mikeblc 

Hi, I have put some additional codes in the attached workbook that forces recalculation and that should take care of the issue you described.

[#15]

@Jamil Mohammad 

is there a way to count color by CF when CF is not using a formula.

i have attached a my file. the CF is set by colmun and i need to count the cells by row.

i managed to get it to count with the following code but i have an auto sort macro in place and as soon as it sorts i get a #Value error.

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

I tried to remove the auto sort to see if that worked and then the count is not correct.

Thanks,

JW

[#16]

@JonathonW 

you do not need a UDF for this. what you are trying to achieve can be done using built-in Array formula.

here is the formula =SUMPRODUCT(1-(COUNTIFS($B$4:$AY$4,$B7:$AY7)>0),(--($B$4:$AY$4<>"")))

placed this in BA7 and dragged down

Please see your example file attached.

Hope it helps.

[#17]

@Jamil Mohammad 

Hi Jamil,

I am trying to highlight cells with duplicated text that also have an overlap in dates on a project planner. I have tried to count the name of the team involved and the number of coloured cells on the same date and if the return is above 1, then highlight the team cell (Column G) in red to show the duplication.

My trouble is finding the duplication overlap on a number of highlighted dates.

I feel i may be over complicating this though.

I am not sure if the duplication option in the Conditional Formatting can do this.

I have attached a dummy.

[#18]

Hi,

You need to look at my earlier posts and see how did I provided solution for other users. Sorry, don't have time to build a custom new solution particular for your file.

[#19]

@Jamil Mohammad 

Hi Jamil, 

Your help with every one on this subject has been a great help! I have read this post and haven't been able to find anything to help me with the problem that I'm running in to. For some reason the totals by color are not correct. In the attached excel sheet; in Column F, I've set up a Conditional Formatting so that if Column E is greater than 1 but less than 200 it color fills to the light blue, for each row and set up different Conditional Formatting. Then I have the formula SumConditionColorCells on F25 for the total. The values of the light blue cells is really $327.60 but the =SumConditionColorCells is totaling it at $437.28. I saw a post on a rounding issue, I used that code and the original code that was posted and still getting the same value. 

Any way that you could take a look? 

Thanks! 

[#20]

@Jamil Mohammad 

Thanks, will have another look through. Appreciate your time.

[#21]

Dear @Jamil Mohammad 

I'm experiencing issue with sum of CF cells. I have cells that colors in a dinamic way:

If the sum is less than D3, color the cells in green

If the sum is between D3 and D3+D4 color them in yellow

If the sum is between D3+D4 and D3+D4+D5 color them in red

Colouring works fine, but when I try to sum the value per color,  only summing green works, the others return in a #VALUE error.

I believe I've done not a proper color formatting.

Can you please help out?

Thank you.