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,

 

I have been able to get the UDF to function successfully.  However, when I refresh the data on a different sheet to update that sheet's count of colored cells, the sums change on the previous sheet and/or any other sheet which has summed the cells with conditional formatting.  The cells within the sheet that I refresh on sum correctly, so my conditional formatting should be accurate.  Do I need to establish the UDF to each sheet in the workbook specifically, or is there another way to ensure that the sums calculated on one sheet do not change when a refresh has been run on another sheet?  Would there be anything else I would need to check as to why this is happening?

 

Thank you for your help!


you did not attach anything, besides your question is not relevant to this thread. try posting a new question, 

 

@aditya wrote:

Hi jamil,

I'm little lost & confused and trying to make my code bit short.

 

In multiple if, Elseifs statements in VBA, I need to use array, But not able to debug it.

 

Excel version of same is attached, Can you help me with vba code..

 

Capture.PNG


 

Hi Steve,

I am not sure if I understood your question.

The UDF calculation method works exactly as the whole workbook calculation like F9 key.
meaning if the calculation is triggered, it will effect all sheets, and not only a specific sheet.
so, if you want the UDF to only calculate on specific sheet, then the code needs to be modified to work on the worksheet level.

Hi Jamil,

 

When I refresh using F9, the current sheet will show the correct data in the output cell, however this appears to interfere with the output cells on other sheets in the workbook which are trying to sum the color condition using a range of cells on their respective worksheet.  I have attached a sample of the workbook I'm working on to hopefully better illustrate the situation.  Perhaps this is an issue with my conditional formatting formulas, however each sheet is calculating the correct number of colored cells on its own, leading me to believe that this is not the case.  Could you take a look and suggest what I might do to fix this?  If it requires a UDF for each sheet individually, could you help me with a rewrite to make the code sheet specific?

 

Thank you very much for your time!

Hi Steve,

 

thanks for uploading the sample data.

 

Can you please test the attached version?

 

 

Hi Jamil,

 

I am running into an invalid name error with each COUNTConditionColorCells output cell stating that the formulas contain unrecognized text.  I did enable macros on the sheet after downloading it.  However, I can see from the document preview that the first 2 sheets are calculating correctly for both green and red text cells, while the 3rd and 4th sheets are calculating green text cells properly but not the red text cells (sheet 3 shows 6 while it should be 9, sheet 4 shows 10 while it should be 7).

 

Thank you.

Jamil,

 

Please disregard my previous message.  After I enabled Macros, saved, and closed the document, I opened it back up and it was running the formulas properly.  However, I did have to double click the cell and press Enter to refresh the formula so that it would calculate the correct amount.  Regardless, the numbers work and they do not change when I do this to other output cells on separate sheets.  I will need to add what you did on these 4 sheets to several other sheets to get them to work properly as well.  Could you let me know what you changed so that I can make the same edit to the full workbook and its additional sheets?

 

Thank you!

Thanks for the feedback Steve.

I didn’t change anything in the sheets.

Except that I removed one line from the beginning of the code which was
Application.Volatile

That’s it.

If you add more worksheets there should not be any issue.

Jamil,

 

I have made this adjustment after adding more sheets and all is working just as it should.  Thank you so much for all your help on this issue!

You are welcome Steve.

Thanks for the feedback.

Hi Jamil,

 

Thank you for sharing your VBA code.

I have copied your code in my excel file, but I got #VALUE! errors.

Could you check what's wrong with the file?

Thank you for your help in advance.

 

 

 

Jamil I have the same issue as Hyosun.  I've entered the VBA code but cannot get it to work.  No matter what I do it keeps showing the #Value.  I'm trying to count duplicate text based values which seems to be causing some problems. Can you point me towards where the error might be occurring and or a resolution.

 

Thanks

 

Noel

Hi Hyosun Ko,

 

the reason it did not work is, because you used ranges outside the scope range and worksheet in the formula that set conditional formatting.

 

UDF is not designed to handle such complicated formulas outside the scope of CF.

 

however, the good news is that you do not have to use the UDF to ge tthe result which you need.  you can use the built-in SUMPRODUCT function to get the sum based on your conditions exactly like the CF.

 

so for sum of Cabinet use =SUMPRODUCT(G$11:G$20,--(G$11:G$20>0),--ISNUMBER(MATCH($B$11:$B$20,Cabinet_recharge,0)))

 

and for base =SUMPRODUCT(H$11:H$20,--(H$11:H$20>0),--ISNUMBER(MATCH($B$11:$B$20,Base_recharge,0)))

 

for colour =SUMPRODUCT(J$11:J$20,--(J$11:J$20>0),--ISNUMBER(MATCH($B$11:$B$20,Colour_recharge,0)))

 

for installation =SUMPRODUCT(K$11:K$20,--(K$11:K$20>0),--ISNUMBER(MATCH($B$11:$B$20,Installation_recharge,0)))

 

for delivery =SUMPRODUCT(L$11:L$20,--(L$11:L$20>0),--ISNUMBER(MATCH($B$11:$B$20,Delivery_recharge,0)))

 

I have embedded these formulas in the attached workbook.

Hi Noel,

The VALUE error because your conditional formatting was not set using formula.

If you read my earlier post, I have highlighted that UDF only works if the conditional formatting is set by using formula and not the built-in features of CF.

 

So, in the attached workbook, I have set the Conditional Formatting using formula and then the UDF works.

 

please note that SUBTOTALs are not counted as duplicate.   please see attached file and examine it by yourself.

 

Hope it helps.

 

 

Thank you Jamil- so easy when you know how.  Much appreciated.

Dear Jamil,

 

Your help has been really enlightening. If you are available, could you please help me by taking a look at his file?

I am using the VB code you created, but the formula doesn't seem to recognize the CF color and I keep getting the #VALUE! refference.

Could you let me know how to fix this?

 

 

Dear David,

 

The issue with your file was, that UDF was using single row range as the range input, while the Conditional Formatting was applies from L2 to Z8 .   So, i modified the file Conditional Formatting and now it works. 

 

Also inside the CF formula, you used two functions, while this was not cause of error, but to make it simple and faster, i simplified it with a single function of "Columns".

 so =COLUMN(L2)-COLUMN($L2)+1>=$AE2  became =COLUMNS($L2:L2)>=$AE2

 

 

Please download and see attached workbook.  

 

This is awesome, thank you so much for your help!

Jamil -  This is a great tool, however, is it possible to make it work for 3 colors?  The counts and sums are inaccurate for me after the first color when there are more than 2.  Thank you!