- last edited on
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
03-10-2018 09:53 AM
Do you have a sample excel file where you use the count by conditional format?
I am having problems using it.
03-15-2018 02:15 PM
03-15-2018 02:54 PM
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.
03-19-2018 03:03 AM
03-19-2018 09:58 AM
When I use the code, it shows #VALUE!. This is happening once the condition changes.
I am using Excel 2016 with Windows 10 Pro.
03-20-2018 07:42 PM
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.
03-21-2018 11:16 PM
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.
03-22-2018 05:29 AM - edited 03-22-2018 05:34 AM
If you mean the font color set by conditional formatting. then you can change the line
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
If CellsRange.FormatConditions(CF1).Font.Color = ColorRng.Font.Color Then
Please see it in the attached sample workbook.
03-22-2018 05:36 AM - edited 03-22-2018 05:47 AM
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.
03-22-2018 07:35 PM
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?
03-23-2018 03:28 AM
I have modified the code and attached is the version that counts instead of sum.
03-23-2018 07:44 PM
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.
03-26-2018 10:03 AM
03-27-2018 07:56 AM - edited 03-27-2018 08:14 AM
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
03-27-2018 08:19 AM
03-31-2018 10:12 PM
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!
04-11-2018 10:21 AM
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
by Pau_Me on September 19, 2019
by matt nipper on December 20, 2016