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 GSMCONNECT, Mould Template1.xlsm is not my file, if you downloaded from one of the answers in the thread, it could be my reply to one of the questions in the thread. so, I did not ever create such file. However, I quickly looked at the file you shared. I do not get any #VALUE error. Perhaps, it is some Add-In or any other file in your excel start up folder that interferes with the UDF. Try to disable them and then the error may disappear.

Hi Jamil,
thank you for your reply.
I sent the file to a friend of mine and he gets the # VALUE error also.
could this be a language problem? I'm from Belgium and our excel is in dutch, not in English.
thx
HI GSMCONNECT, Yes, it could be the language issue, but I am not sure.

in B51 you can use a formula and completely disregard the UDF. a formula to get that result is =SUMPRODUCT(($P$9:$P$18)*(ISNUMBER($P$9:$P$18))*(LEN($P$9:$P$18)=1))

and in Dutch =SOMPRODUCT(($P$9:$P$18)*(ISGETAL($P$9:$P$18))*(LENGTE($P$9:$P$18)=1))
Unfortunately, I have still not been able to sort out my count issue; if anyone is able to review my sample spreadsheet and provide some guidance it would be appreciated. Thanks, Dave
please share a sample spreadsheet.

@Jamil Mohammad Sorry, fairly new at this and thought my reply would be attached to my original post and sample. Nonetheless, thank you and please see the attached.  

@Jamil Mohammad 

Hi Jamil,

thank you very much for your reply

I started excel in safe mode and opened the file but still get a # VALUE error.

I also pasted the sumproduct formula you posted but still get a # VALUE error.

then I opened the file in excel 2007 which was installed in English but still get a # VALUE error.

the fact is I need a double condition in the CF of my excelfile.

can you please help?

thx

Hi @GSMCONNECT 

 

you misunderstood on the use of the SUMPRODUCT formula. it was not meant to be used inside conditional formatting. it was meant to replace the UDF. I have now placed three formulas in cells B51, C51, and D51.
the formulas work just fine, so you do not need to use UDF SumConditionColorCells

plz see attached file with the formulas.

@DBrydon 

 

Hi,   

 

I do not think you need a overcomplicated UDF for this. you can get the same result using built-in formula =SUMPRODUCT(COUNTIF($F$18:$L$28,$F32:$O32))

 

Please see attached file with formula.

Jamil, thank you so much! I've just had a quick review and your solution works wonderfully for my needs and has helped tremendously. Again, I truly appreciate your patience and guidance, especially with such a novice as myself. Take care and I hope you are well. Cheers, Dave
Dave, You are welcome. I am glad I was able to help.

thanks for the feedback.

take care, cheers, Jamil

@Jamil Mohammad 

Hi Jamil,

 

I have been reading your posts and you are able to solve all issues regarding the count/sum of conditional colored formatting cells.

I have tried using the UDF you mentioned for the count but I am not getting any result.

I have attached my file here with the desired output. Can you please look into this? I have looked at multiple solution and nothing is working so far.

 

Your help is greatly appreciated!

@Nidhi1400 

 

Hi,

 

you do not need a complicated UDF for this. you can simply get the result by =SUMPRODUCT(--(A2:D2<$A$8:$D$8))

 

please see it in the attached workbook.

@Jamil Mohammad 

 

The count worked without the UDF!

Thank you so much Jamil for your quick reply and the feedback.

 

Have a great day.

@Jamil Mohammad Dear Sir,
I tried to use your code but seem it does not work for my file. Kindly help me to check and correct if available

@TomNguyenvn 

 

you conditional formatted applied ranges were not the same as the range used in the UDF. so i changed the range of the CF and it works. please see attached.

@Jamil Mohammad Hi Jamil, i've tried the vba you provided to sum colored cells from conditional formatting. But it doesnt work. Could you take a look my worksheet to see where the code went wrong??

 

Thank You.

@Anton_Nugroho_023

the conditional formatting applied range and the range use in the UDF are not the same. that is why it does not work.

@Jamil Mohammad , I've done everything as mentioned in this thread but I still get the #value message.

 

The cells I want to count are colored via Conditional Formatting.

The reference cell, I tried it with Conditional Formatting and without but with no luck.

Both times I get the #value error.

 

When I give my reference cell a different color with CF, then I still get: #value

When I give my reference cell a different color without CF, then I get: NO-COLOR.

 

Any clue where I go wrong?

 

In my attached example, the formula is in cel BD7.