Dec 20 2016
- last edited on
Jul 25 2018
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
Mar 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.
Mar 15 2018 02:15 PM
Mar 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.
Mar 19 2018 03:03 AM
Mar 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.
Mar 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.
Mar 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.
Mar 22 2018 05:29 AM - edited Mar 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.
Mar 22 2018 05:36 AM - edited Mar 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.
Mar 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?
Mar 23 2018 03:28 AM
I have modified the code and attached is the version that counts instead of sum.
Mar 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.
Mar 26 2018 10:03 AM
Mar 27 2018 07:56 AM - edited Mar 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
Mar 27 2018 08:19 AM
Mar 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!
Apr 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