- 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.
12-08-2020 11:30 PM
@Jamil Mohammad Hi Jamil, thank you for this wonderful formula, it's my first UDF and was very easy to set up. I have the same issue as others regarding the fact that the formula only works for the first column and then every column after is returning the same count as my first column - however this link you're referencing appears to be dead. I'm hoping you can help me by resharing the link to how to resolve this issue and/or video. Here's the formula returning the same answer for every section (I counted and it should not be 9 for every single month). Thank you!
12-26-2020 05:22 PM
Hello, I have a similar situation but I can't make it work.
I need to count how many cells in a row range have green CF, and then it is also needed to sum the values of those cells found. Obviously, the result of counting will be in one cell and the summing will be in another.
The excising (and working) conditional formatting is set to find the largest number per column, for obvious reasons. Within the table, the data has been divided into two ranges/groups, but it is still one table. The data has a Table Style.
In the attached file, the corresponding calculation area (which I can't solve) per row is to the right of the table, in columns P, Q, R, for the first range (Group 1), and T, U, V for the second range (Group 2).
I wrote some explanation and a sample of what is needed inside the file.
Thanks you so much in advance.
01-17-2021 12:24 AM
Hi, it is because inside the UDF you are using absolute column reference and when you copy it to the right, the same reference copies and that is why you get the same result. using F4 key, try to change the column reference to relative reference.
01-17-2021 12:29 AM
Hi, if you read my earlier posts in the same thread. you will see that many users made the mistake of not using consistent range within the UDF argument and the CF applied range. Same has happened in the file you shared, if you look your CF applied range is applied vertically, while in the UDF the counting rate is horizontal range. This will not work, unless both CF range and the range used in the UDF are the same dimension. Hope it helps.
01-17-2021 01:46 AM - edited 01-17-2021 01:49 AM
Thanks for replying.
About what you said that:
"your CF applied range is applied vertically, while in the UDF the counting rate is horizontal range. This will not work, unless both CF range and the range used in the UDF are the same dimension."
"This will not work" is not accurate, fortunately. See attached file. A solution was provided already a few days ago elsewhere and I have attached it here for everyone's benefits. The solution only uses a formula, no VBA needed, although I'm sure it can be done that way too. This solution allows the CF going vertically and the counting/adding horizontally, just the the way I have it and is needed.
If you study the file, you will see that because of the type of data that is being evaluated, it must be laid out in a certain way and the conditional formatting must be applied per column, vertically, and independently of the other columns. Then, the counting of those CF must be done horizontally, per row, to be able to pinpoint the higher match. Of course the data can be laid out in many other ways, which will give a different perspective, but the one we need is this one at the moment.
What's the point of using Excel if we have to maneuver the data according to the limitation of Excel instead of the other way around?
Thanks. Stay safe.
Glad to hear that you found a solution. If you read my earlier posts in this same thread, you will see that I have offered solutions to some users using similar method you found using SUMPRODUCT. I always mentioned in this thread that these UDFs have limitations and can serve as suboptimal solution, while there is always better ways to get the same result using formulas, but not many people can manage to write those complex formulas, so they go with the suboptimal solution of UDF by giving the ranges the modification needed to make it work.