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

Hi @Jamil Mohammad 

 

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.

@jvass92 

 

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.

 

 

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.

@jvass92 

 

In that case, you can use the attached workaround.

@Jamil Mohammad 

 

Is there a way that the colour can just remain in the first column?

Any update at all?

@jvass92 

 

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.   

@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!

cvgme_0-1607499006489.png

 

@Jamil Mohammad 

 

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.

@cvgme 

 

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.

@Z Z 

 

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. 

@Jamil Mohammad 

 

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.

@Z Z 

 

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.

@Jamil Mohammad 

Hi Jamil; I'm a complete novice in Excel so please be patience :)

 

I have a spreadsheet that manages a groups lottery numbers; I enter the actual drawn numbers into their respective cells, and any matching numbers from the group members chosen numbers are highlighted by Conditional Formatting, i.e., =COUNTIF($F$18:$L$28,F32)>=1

 

Fortunately, I found this thread and have tried to incorporate your “COUNTConditionColorCells” VBA function? Unsure of correct terminology. Additionally, I have downloaded and reviewed your example “COUNTBYCOLOR.xls” and have tried to use it for my needs without success. I have also read through the many conversations but found it to be confusing me more than resolving the issue, i.e., I tried to implement several similar situations/examples, but this only muddied the waters so to speak.

 

Unsure, but suspect my issue “may” be in my use of the “=COUNTIF($F$18:$L$28,F32)>=1” formula for conditional formatting. It highlights well, but regardless of what I try, the best I can achieve is a return of “NO COLOR”, where I require a count of the highlighted coloured cells in the row.

 

The attached spreadsheet is for your review and hopeful resolution.

 

Thank you and I look forward to your guidance.

Dave

Jamil,

 

Feel bad for bothering you with what I think is going to be a simple fix, but for the life of me I can't figure what I'm doing wrong here.

In the attached, which I've downloaded from one of your posts, I have a series of conditionally formatted cells (for instance AJ9:AU9) which when TRUE one or more cells will turn purple.

I'm trying to use your formula in cell AX9, and the minute I try to change the RANGE to AJ9:AU9, checking against color purple in cell AW9, I get "#VALUE".

If you don't mind, why does it error out?

I appreciate it, and hope you have a good weekend. Stay safe. ;) 

@Jamil Mohammad 

 

Jamil,

 

Feel bad for bothering you with what I think is going to be a simple fix, but for the life of me I can't figure what I'm doing wrong here.

In the attached, which I've downloaded from one of your posts, I have a series of conditionally formatted cells (for instance AJ9:AU9) which when TRUE one or more cells will turn purple.

I'm trying to use your formula in cell AX9, and the minute I try to change the RANGE to AJ9:AU9, checking against color purple in cell AW9, I get "#VALUE".

If you don't mind, why does it error out?

I appreciate it, and hope you have a good weekend. Stay safe. ;) 

Hi

I will take a look later today. It is too early in the morning here.
You cannot count colors assigned by conditional formatting, as these cells are effectively still the default background color!
Why not just count these cells based on the conditions you used for conditional formatting?

Example:
Set in a worksheet with conditional formatting so that the cell background turns green when the cell contains the value 2. COUNTIF counts all cells that contain the value 2 = all cells whose cell background has turned green due to the conditional formatting

Is simply a quick solution before you start with VBA code.

Thank you for your patience and time.

Nikolino
I know I don't know anything (Socrates)

Hi Jamil,
I downloaded "Mould Template1.xlsm" on one of your posts. For a reason I don't understand I get a "# VALUE" error in B51

it has something to do with the AND function in the CF.

when I don't use the AND function in CF it works.

the fact is I need the AND function in the CF of my sheet

Can you please help?
thx

Hi bobbyfisher, I looked at your file and the reasony ou get the VALUE error, because of the two reasons. A) the conditional formats are not set using formulas B) the range inside UDF and the conditional formatted range are not consistent. If you look at my earlier posts, I have mentioned the three limitations of the UDF. so, unless those conditions are satisfied, the error will not go away.