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]

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

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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.

[#6]

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.

[#7]

Hi Jamil,

I came across your solution to a similar query i have, and tried applying it to my data.

It almost works however the summed total is slightly off the total when i manually sum the data.  it seems like it may be a rounding issue....do you have any suggestions on how to resolve?

Thanks in advance,

Micheal

[#8]

Hi Michael,

there is no rounding issue. my suggestion is to revisit the colors and try to run the test on a smaller data set and see how it goes.    if you can provide a sample dummy example. i can take a look.

[#9]

Hi Jamil,

Thanks for the quick response.  It definitely appears to be a rounding issue.  I looked at a smaller set of data and ran some tests on it.

The data I tested was the following 6 values - 45.18, 45.26, 45.21, 45.39, 45.24, 45.41

The sum value should be 271.69, however the function is returning a sum of 270.00 (which is each value rounded to its nearest whole number - 45*6)

I then changed the first value to 45.68, at which point the function returned a sum of 271.00 (rounding the first value up to 46, and continuing to round the remaining values down to 45)

Unfortunately I'm not in a position to attach any samples, but if there is any reason that you think this issue is occurring then please let me know.

Thanks in advance.

Micheal

[#10]

Hi Michael,

Ok.  Can you please replace the previous UDF with this one attached below. This should fix the problem.

Please let me know how it goes.

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

[#11]

Hi Jamil,

Thanks again for the response.

I've just tried your amended code and it works perfectly.  Your help on this is very much appreciated!

Best Regards,

Micheal

[#12]

You are welcome. Thanks for the feedback

[#13]

Hi Jamil,

I have a GANT diagram for were I want to sum the values in column cells with a specific colour.
This is to sum how many labour hours we have to make each day for a specific background color that was generated by the conditional formatting.

In this GANT diagram a pop up boxt can not be used. Do you have a solution for that?

[#14]

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

[#15]

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

[#16]

Hi t.aerdts,

I think that can be achieved using Formulas with the referred UDF. If you post a sample dummy data here and I will have a look in the weekend. 

[#17]

Hi Jamil,

herewith the sample file.

 appreciate your input.

I put some comment in the file.

KR T.aerdts

[#18]

Hi,

I just noticed that the zip file you uploaded is blank. 

besides, this thread is related to conditional formatting, so i suggest you open a new question with uploading the sample file with it.

[#19]

Hi Jamil,

attached the file with content.

It would be great if the cells can be summed when the color is set by conditional formatting.

inside the excel file I insert some remarks and tekst for declaration.

Tillo 

[#20]

Hi Tillo,

I looked at your file. you are using a incorrect UDF to sum cells based on conditional formatting.

I cannot fill your template for you.  you need to copy the below UDF put it in a module in your workbook.

then use it in your workbook =SumConditionColorCells(CellsRange As Range, ColorRng As Range)

like this =SumConditionColorCells(O$9:O$40,$N6)

I found another problem with your workbook. the color used in the conditional formatting and the cells referenced as the color criteria e.g. $N6 are not the same color.  unless they are the exact same color, the UDF will not work.   I have attached a workbook that shows how the UDF works. so you need to adapt that in your own workbook.

Sorry, I cannot help further on this. 

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

[#21]

Hello!

Do you have a sample excel file where you use the count by conditional format?

I am having problems using it.

Thanks!

[#22]

Hello Bethany,

Yes, I have attached it here.  

[#23]

Jamil, does this work for the color WHITE as well? i seem to get the error "#VALUE!" when using the SumConditionColorCells formula. Other colors seems to work.

[#24]

On your worksheet all the colors work just fine. But on mines, for some reason only 1 out of 3 colors i'm using works, the light blue. Those columns that are highlighted are conditionally formatted using a formula.

I've made 3 sample formula for each of the colors.

[#25]

"does this work for the color WHITE as well? "
There are two things not to mixup the WHITE background color with No-Background Color.

If your cells have conditional formatting and the color is selected is plaint white then yes it works. but if the cells have No color meaning it the background color is None then it does not work.

RE: on your second question:
you need to check the colors of your conditionally formatted cells and the color of criteria. they must match 100%. sometimes, the colors look similar while they are different, best way to guarantee you are using the exact same color is to find that specific color's color code and that you can do by using VBA to find the color code of your cells.
If you do not want to go in that direction then i suggest you rebuild your conditional formatting cells by selecting a simple color and then also use the same color in the criteria cell.
in case if you wanted to find out the color code with VBA then here is a link https://www.thespreadsheetguru.com/the-code-vault/2014/11/5/retrieve-excel-cells-font-fill-rgb-color... that explains how.

[#26]

When I use the code, it shows #VALUE!. This is happening once the condition changes.

I am using Excel 2016 with Windows 10 Pro.

[#27]

Can you post a screenshot of dummy data or a dummy excel file?

[#28]

Hi!  I've also been getting the #Value error.  I copied and pasted the Count version of the VBA.  Screen shot attached let me know if you need more.  I entered the RGB color codes so they are exact.

[#29]

Good evening Jamil,

Do you happen to have a version of this UDF which would sum cells with a given font color rather than an interior/background color?  I have tried changing the word "Interior" to "Font" in the UDF, however when I run the formula on my desired cell, it simply shows "NO-COLOR" rather than adding the cells with the desired conditionally formatted text color.  Any help would be appreciated.

Regards,

-Steve

[#30]

Hi Steve,

If you mean the font color set by conditional formatting. then you can change the line

From

If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then

To

If CellsRange.FormatConditions(CF1).Font.Color = ColorRng.Font.Color Then

Please see it in the attached sample workbook.

[#31]

Sometimes this happens, if you have an external Add-in or there are some codes in the PERSONAL.XLSB workbook.

click on the cell that has #Value error and then press F2 to go to Edit mode and then enter again. if you see the VALUE error disappear that means that the error is caused by some external add-in or your personal.xlsb workbook that is in the start up folder.

If you can share a dummy example file, I can take a look at it.

[#32]

Thanks Jamil, this gets me very close.  However, I suppose what I really need is for this VBA to count the number of cells within the selected range if they are a certain color, not sum the values within the cells, as the values within the selected cell range are text, not numbers.  What would I change in the VBA to accommodate this?

Regards,

-Steve

[#33]

Hi Steve,

I have modified the code and attached is the version that counts instead of sum.

[#34]

Jamil,

This modified code does not seem to be working.  I am getting a #VALUE! error in my target cell that should be displaying the count of cells with colored text in my selected cell range.  Would there be any part of the code I would need to change so that it will work with my cell range containing text?  It doesn't seem to matter whether I choose General or Text for the cell format, the formula still results in a #VALUE! error.

[#35]

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.

[#36]

Still getting the same error, thank you for taking a look, it's attached.

[#37]

Hi Steve,

I have attached the workbook that shows the count of conditional formatted area that has text values in them.  it works.  I have no idea why it does not work in your workbook. Perhaps, your workbook conditional format rules are not set by formulas.  Please read the limitation of the UDF in my post 

Alternatively, If it does not work then I suggest that rather than Counting or Summing based on the outcome of the conditional formatting, you use the same criteria as exists in the conditional formatting to Count or Sum the cells using SUMIFS/COUNTIFS or SUMPRODUCT

[#38]

Hi Cara,

I looked the file you attached. there were many issues that probably caused that error.

A) The workbook had No UDF in it. hence NAME? Error
B) this UDF has two limitations 1- 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 I could see in the workbook you shared that there were two rules which was not determined by formula 2- Second the UDF will only work if sum range is more than one cell in another word, it will not sum a single cell or if Conditional formatting rule is applied in range that is a single cell. In your workbook there were multiple rules that applied in single cells.

As I suggested in my earlier posts, If the UDF works, then fine, if does not work then it is not the only and optimal solution. So, rather than Counting based on the outcome of the conditional formatting, you can use the same criteria as exists in the conditional formatting to Count the cells using COUNTIFS or SUMPRODUCT

I didn't examine in in depth your workbook as the data validation were linked to another workbook and for me it was showing a broken link. You could use the conditional formula to return values in column G and based on the values you could simply count the overdues with simply formula like this =COUNTIF(G9:G28,G5)

[#39]

Jamil,

I am not sure why this would not be working either, as the conditional formatting is being determined by formulas.  Perhaps this is not working due to an add-in (or lack thereof), or perhaps it has to do with the cells with conditional formatting having drop-down lists?  I have attached a draft of my workbook for your reference.  Would you be able to take a look and try to see why the formula based on your UDF won't calculate?

Thanks for your help!

[#40]

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

[#41]

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.

[#42]

Thank you Jamil, 

Is there any solution for that? 

Appreciate your help. 

[#43]

Hey Jamil,

the code is not working on my file, could you please help me.

[#44]

Hi there,

I'm using your code to count my conditional formatting colored cell, but the cell cannot be counted. The code maybe cannot read my colored data cell. Could you please help me? It shows no color when I'm trying to use the CountConditionColorCells formula.

Tq..

[#45]

Hi Fenfen,

you have given the range of cell into the UDF range arguments cells that do not have conditional formatting applied to them.  

UDF will throw error if range of cell given as input that do not have conditional formatting rule applied to it.

so to make your workbook work. you could achieve the result by using a combined addition 

=COUNTConditionColorCells(B3:I3,J2)+COUNTConditionColorCells(B8:I8,J2)+COUNTConditionColorCells(B13:I13,J2)++COUNTConditionColorCells(B18:I18,J2)

see attached file.

[#46]

Hi ogyramly,

Please read the earlier posts through this thread and try the suggestion given and the limitations of the UDF, i suspect that one of the issues already known could have caused the UDF to return no color.

[#47]

Hi, thanks for your respond.

Does the formula of conditional formatting for format cell that contain can be applied using this code??

I cannot link my problem with the others as they might using different conditional formatting formula..

[#48]

H

---

@Jamil Mohammad wrote:

Hi Fenfen,

you have given the range of cell into the UDF range arguments cells that do not have conditional formatting applied to them.  

UDF will throw error if range of cell given as input that do not have conditional formatting rule applied to it.

so to make your workbook work. you could achieve the result by using a combined addition 

=COUNTConditionColorCells(B3:I3,J2)+COUNTConditionColorCells(B8:I8,J2)+COUNTConditionColorCells(B13:I13,J2)++COUNTConditionColorCells(B18:I18,J2)

see attached file.

---

Hi Jamil,

I have tried, but its not working. cause my the color will be change anytime. please see sample file.

[#49]

Hi Fenfen,

It is because the conditional formatting formula of Vlookup uses the same range that is used in the formula on the cells that produce the numeric results, so it is kinda create circular reference in a nontraditional way. The UDF is not designed to handle this sort of complex scenario. the UDF works on the simple conditional formatting that would use a formula that evaluate cells that has either simple values or if they have formulas, they are not using the same range as the formula used in the conditional formatting.

I suggest you use the other methods described in the earlier posts, like combination of SUMPRODCUCT with COUNTIFS

[#50]

Hi, this is exactly what I'm looking for but I just need count not sum.  Is it easy to re-produce this as a countif?