Dec 20 2016
- last edited on
Jul 25 2018
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
Mar 17 2021 08:32 AM
Mar 17 2021 08:49 AM
Mar 17 2021 09:12 AM
Mar 17 2021 09:31 AM
Mar 17 2021 10:20 AM
@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.
Mar 18 2021 03:39 AM
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?
Mar 18 2021 07:39 AM
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.
Mar 18 2021 07:54 AM
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.
Mar 18 2021 08:05 AM
Mar 18 2021 09:15 AM
Mar 18 2021 01:48 PM
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!
Mar 18 2021 06:19 PM
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.
Mar 19 2021 10:23 AM
The count worked without the UDF!
Thank you so much Jamil for your quick reply and the feedback.
Have a great day.
May 05 2021 06:00 PM
@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