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
Jul 18 2018 08:59 PM
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!
Jul 27 2018 06:09 PM
you did not attach anything, besides your question is not relevant to this thread. try posting a new question,
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..
Jul 27 2018 06:11 PM
Jul 27 2018 06:16 PM
Aug 01 2018 08:22 PM
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!
Aug 02 2018 08:43 AM
thanks for uploading the sample data.
Can you please test the attached version?
Aug 02 2018 10:06 PM
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).
Aug 02 2018 10:16 PM
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?
Aug 03 2018 12:49 AM
Aug 04 2018 02:54 PM
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!
Aug 23 2018 07:37 PM
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.
Aug 23 2018 09:43 PM - edited Aug 23 2018 09:55 PM
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.
Aug 30 2018 04:15 AM
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.
Aug 30 2018 04:18 AM
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.
Sep 06 2018 07:34 AM
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?
Sep 07 2018 02:47 AM
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.
Sep 19 2018 11:36 AM
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!