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

Hello!

 

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

I am having problems using it.

 

Thanks!

Hello Bethany,

 

Yes, I have attached it here.  

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.

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.

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

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

 

I am using Excel 2016 with Windows 10 Pro.

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

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.

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

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.

 

 

 

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.

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

Hi Steve,

 

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

 

a.png

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.

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.

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

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

 

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)

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!

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