sum by color when colors are set by conditional formatting

[matt nipper]

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

[#2]

@hdemark 

Hi,   It did not work, because if you read my earlier posts, I mentioned that UDF only works if your CF range and UDF input range are the same. in your file, they were not the same.

you can use built-in function to acheive the same result. see the formula in the attached workbook.

=SUMPRODUCT(F4:F23,(E4:E23>=1)*(E4:E23<=200))

[#3]

@mtommy84 

In the file you uploaded, the input range for UDF was wrong. I have corrected it and it works fine.

attached is the file.

[#4]

Dear @Jamil Mohammad 

I see the correction in the preview, but when I download the file, I still get #VALUE! and the range in the UDF is still the same.

Can you write the proper function for E4?

Thanks,

Tommaso

[#5]

@mtommy84 

try this one, and if you still get error, then you need to remove other functions you have put the in the module.

[#6]

@Jamil Mohammad 

I removed all the functions from the module, but still, #value.

I'm a bit lost. Why is that so?

Cheers

[#7]

@mtommy84 

I checked the file is fine.  It works in my machine.  

Sometimes the #Value error gets triggered by some other Add-Ins or Personal.XLSB

before you open the file again, disable any add-ins and personal.xlsb file.

try to see if you have any file in this directory C:\Users\YOURUSERNAME\appdata\Roaming\Microsoft\Excel\XLSTART

if there is try to move it temporarily. 

also if you have any Add-In Installed, try to disable the add-in.

[#8]

@Jamil Mohammad 

I have tried working with your formula on a dataset, I want to count the formatted cells within one collumn. However the formula keeps returning an error: "#VALUE!" or keeps returning the value 0.

I think I have correctly added the macro. Also I checked the colourvalues, but they seem to be the same..

Do you have any suggestions for solving this issue?

Thanks in advance!

[#9]

@sanderubels1 

I looked at your file. the reason you are getting error, because you did not set the conditional formatting rules using formula.  if you read my earlier posts in this same thread you will see that I have mentioned that for the UDF to work, the conditional formatting needs to be set using formula. 

for example, I have changed the yellow part of the conditional formatting in your file and the UDF worked.  Please see attached file. you need to change the other conditional formatting the way I changed it and they will work.

[#10]

@Jamil Mohammad 

Jamil, you seem to have a really good handle on this, so I was wondering if you could look at my attached file and help me out.

As you can see, column AY displays sum from columns D through J. I've set conditional formatting to in AY depending on what is displayed in column B. Just as a test, I put dummy data in column D (COOP/COG at 1, DMP at 2, LEOP at 3, etc...). The conditional formatting works perfectly in column AY.

I then tried to use your formula "SumConditionColorCells" in column Q, expecting to see it going in order (COOP/COG 1, DMP 2, LEOP 3 etc...). For some reason DMP is displaying 1 instead of 2, down the list, Conduct is showing 3, File Management 8, etc....)

Do you know why it's not working? Thanks in advance for any insight you can provide.

[#11]

Hello @Jamil Mohammad,

I see you have been working with everyone on this great code for a couple years now - thanks!

I have a spreadsheet that tracks many different changes with many different functional groups. Each cell is a task to complete and I use your code to sum up the open, late, or closed items by each group I manage via the background color.

The cells have these color calls outs when applied to the CF I have prescribed:

1) Grey (when identified as grey using a different code); 2) Yellow (=""); Green (>today()); Red (<= today()); and finally; Blue for complete (manual change in background color with the conditional formatting to change the text to white). If a date is missed the team member presses Alt + Enter and places the next target date in which that can get done.

My issue is that when items are going from Green to Blue the counter now counts the one cell as both Blue and Green. Also, when a team member places another date in the cell after it is late the CF changes it to Red based on character count but the counter counts both it as Red and Green. Not sure what is happening, appreciate any help Jamil. I have attached a small sample set of data that I am vetting out before applying to the much larger tracker.

Thanks!!

[#12]

@loganl84 

Not sure if I understood the problem. as you are saying that DMP should show 2 instead of 1. however, there is only one color assigned for DMP and in the list of AY column only one time the DMP color exist. that is why it shows 1.  

the reason for "Conduct" it shows 8 because you are using the SUM version of the UDF. so there is a cell with color associated to the "Conduct" which happens to have the value of 8 and therefore UDF correctly returns that value.  If you want to count instead of SUM. then attached is the version to count. but since all of the cells in AY has only single color, the count returns 1.

 please see attached.

[#13]

@Johnnyb7277 

Hi Johnny,

It was easy to spot what was causing the additional count. It is the UDF "IdentifyColor" you are using inside the Conditional Formatting. Therefore the UDF COUNTConditionColorCells counts them, because blue color criteria overlaps with the green criteria.  for example. Green color condition is that if Date is greater than today. Hence, there are three dates that are greater than today in the list and hence UDF returns 3. disregarding the fact that there is another overlapping coloring applied by CF I=IdentifyColor(A1)="002060" 

the UDF COUNTConditionColorCells works based on the CF when returns True, if there is a overlap, it does not look at the color, it looks at the condition you applied for specific color. 

The solution for the overlap of blue is to simply subtract the value of blue from the count of green as shown in the attached workbook.

In regard to the green and red. there is no overlap, so it works.  the green and red are not double counted.  Please see attached file.

[#14]

Hello @Jamil Mohammad 

Appreciate the quick feedback, your response made me update the Conditional Formatting instead of subtracting one count to another as I am worried with it managing if I scale up and roll out to other teams. I would rather have the CF stand on its own.

You are the man sir and your Code has definitely helped me look like a rock star at work!

Thanks again.

[#15]

Thanks!

[#16]

@Jamil Mohammad 

Having an issue scaling this up without it lagging ALOT. Is there a way to make the CF non-Volatile? I am tracking close to 1000 cells of data constructed by 4 CF rules. But it takes forever to move around the document even though it does exactly what I need it to do. Is there a way to have a "Pause/Refresh" button for the CF while still having the CF freeze it's values where they stand before "pausing" the system and then updating once "Refresh"?

Appreciate the help Jamil!

[#17]

@Jamil Mohammad 

Having an issue scaling this up without it lagging ALOT. Is there a way to make the CF non-Volatile? I am tracking close to 1000 cells of data constructed by 4 CF rules. But it takes forever to move around the document even though it does exactly what I need it to do. Is there a way to have a "Pause/Refresh" button for the CF while still having the CF freeze it's values where they stand before "pausing" the system and then updating once "Refresh"?

Appreciate the help Jamil!

@Jamil Mohammad 

[#18]

@Johnnyb7277 

You need to remove this line of code from both of the UDFs Application.Volatile

both from COUNTConditionColorCells and from IdentifyColor

this should solve the problem

if the recalculation do not stop, then you can temprary disable UDFs by replacing =COUNTConditionColorCells   with @=COUNTConditionColorCells  and once you do all the changes, then replace back to the original state.

[#19]

@Jamil Mohammad 

Thanks Jamil, that definitely moved things along. I have one more puzzle for you sir.

I have a sheet that tracks multiple changes, all of these changes have the same 34 deliverables by 6 different departments. So I sum up the number of red, green, blue, etc...on the bottom of each column and then sum the departments number of red, blue, green, etc...into my tracker.

The issue I am having is that when I sum by column it only takes the first column's CF values and applies them to every other column's sum function. I have attached an example as it is hard to explain. Again, really appreciate your help in these, almost there!! 

[#20]

@Johnnyb7277 

I looked at your file. Your range of CF do not match the range in the UDF. If you read my earlier posts, I have reiterated in several posts that the range in the CF should be consistent with the range used in the UDF. So, I changed the CF for that region to match with the range used in the UDF. Another issue that I found with your file is that you are also using the cell backgroud coloring in the same area where conditional formatting is used. you cannot use the UDF when you color the cell using both CF and manual changing of background coloring. you should set the CF in a way that there should not be a need for manual change of color. for example the range there already had a pre defined blue colors, so when CF criteria did not meet, the cell original pre defined color was visible and hence the UDF was returning incorrect result. I have removed the pre-defined cell colors and left only CF. I suggest you do not use the method of manual coloring as you have described in the criteria for blue cell. Perhaps, you are overkilling this with complicated solution while this can be done easily with built in formulas and CF alone. I have attached the file for your easy reference, try to examine the CF rules.

[#21]

Thanks again @Jamil Mohammad!

Everything is working great from all your help but I have one issue that keeps popping up. I have multiple columns that have independent CF that are summed by the CF per each column. I have different people assessing the file and updating it with dates. Randomly, or at least I see it as random, is that all the CF is combined across all the columns. All the CF works perfectly fine still but the CountConditionalCells UDF then only uses the first column and applies that summed values across all the columns. I then have to go into each column and reapply unique (but identical) CF. This is quite frustrating and I am sure it is something I am doing incorrectly. Any help would be greatly appreciated, I read through the earlier posts and could not find this failure mode discussed.