- 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
06-15-2020 12:35 AM
I've uploaded an example file.
The number in A12 and below changes colour twice. First data in F12 changes it to orange, then I12 changes it to green.
06-15-2020 12:50 AM
As I have rightly guessed. your conditional formatting rules were overlapping each other.
colors were all applied in the same column, while conditional rules were from different columns.
so, if you apply the conditional formatting columns in the columns were they are relevant then calculation will be correct. plz see example file.
also alternatively, you can also get the same result by using SUMPRODUCT function without the need for the UDF. please see example file of sumproduct.
06-15-2020 04:41 AM
Hi @Jamil Mohammad ,
Thank you for the response, however, neither spreadsheets work as planned.
1) The number of 'jobs ongoing' (red) stays the same - which it shouldn't do. When your put a date in the column for inspected, the red should go down one, and orange up one. Then again, when you put a date in date completed, green should go up 1 and orange down 1. As it stands, it looks like X amount of jobs are ongoing (not inspected) when in fact Y jobs have been inspected and should be taken off the red colour.
If this makes sense?
The job number column must also retain the colour.
06-16-2020 01:42 AM
Is there a way that the colour can just remain in the first column?
07-09-2020 07:00 AM
Hi, I think I provided you with a solution.
Regarding your question, you cannot keep multiple colors in the same cell in first column.
Now, we cannot tell which color should prevail other and if one shows, it will misinterpret the condition as the second and third color will not show.