Dec 20 2016
01:16 PM
- last edited on
Jul 25 2018
09:33 AM
by
TechCommunityAP
Dec 20 2016
01:16 PM
- last edited on
Jul 25 2018
09:33 AM
by
TechCommunityAP
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 09:54 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
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
Mar 18 2021 07:39 AM
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.
Mar 18 2021 07:54 AM
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.
Mar 18 2021 08:05 AM
Mar 18 2021 09:15 AM
Mar 18 2021 01:48 PM
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!
Mar 18 2021 06:19 PM
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.
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
May 06 2021 10:14 AM
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.
May 07 2021 01:12 AM
@Jamil Mohammad Thank you so much, sir !
May 19 2021 09:42 PM
@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.
Jun 17 2021 10:25 AM
Jul 23 2021 12:55 AM
@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.