SOLVED

sum by color when colors are set by conditional formatting

New 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

 

293 Replies
Matt,

it would work with any number of colors, as long as the UDF parameter color input and CF are having the same color. if your file does not produce correct result, can you attach a sample file so that i can take a look?

Thank you, I looked at this further and realized my conditional formulas overlapped but their organization was allowing the formatting to be correct.  I am able to change the formulas and get the correct results.  

Thanks for the feedback Matt.  Glad to hear you figured it out by yourself.

Hello,

 

I have a list of expenses that are separated into categories.  When you select a category from the dropdown, it changes background color for the expense amount.  I am trying to add all these up automatically at the bottom.  I have attached a sample.

Any ideas if there is a easier solution, or what I should do?

I tried your second code you posted on this thread, when I try to add the "car" category, it returns with a straight $100.  Dosnt add anything.  Any help would be great.  Thank you very much!

Hi Shane.

 

you can simply do that with SUMIF function.

 

I have embedded the formula into the attached workbook.

Wow, I was WAY overthinking this problem...You're reply was very very quick, thank you.

I was looking on page 2 and found another solution you gave "=SUMPRODUCT(--(ISNUMBER(SEARCH("Car",$C:C))),$D:D)"  But the sumif is way less complicated.  Thank you!

You are welcome Shane. Thanks for the feedback.

Hi Jamil,

Thanks for your patience and replying to queries posted around this subject. Appreciate it. I have been trying to apply the VBA function you had shared at the beginning of the thread. I have a slightly complicated conditional formatting set (cell to be highlighted yellow if there are 3 consecutive zeros, red if there are 4 or more consecutive zeros). I am not sure if it is due to complicated conditional formatting, but I get an error #Name? as I try to count number of cells in red and yellow. When I rerun, sometimes I get a value but the number displayed is incorrect. Could you help resolve - thanks a lot. I have attached the excel.

Hi Anand ,

 

I could not debug your file, aas you had several conditional formatting with deleted reference with #REF error inside your CF formulas.

 

However, i tried to come up with a solution using helper columns and built-in Excel functions.  I have attached the file.

 

 

Thanks a lot for the reply, Jamil. This is very helpful. Thank you for taking the time to help. A quick question, for some reason, when we double click or change the range in the helper column formula, it changes to #value. I am not sure why. If you have any thoughts, please let me know. I really appreciate your support.

It is because those formulas are not simple formulas and they are called Array formula which needs to be entered by holding Control + Shift then+ Enter
So if you simply enter it will give Value error but if you enter it with hold together control shift enter then it will work.

Got it. Thanks a lot, Jamil. Again, really appreciate all your help and quick responses.

Hello, Jamil,

 

First I'd like to thank you for answering all those questions!

 

I've been trying to use the count formula in a worksheet but I always get the #Value error. I read the whole thread and I couldn't figure out what I'm doing wrong. Could you please help me?

 

This spreadsheet has grades of my students in three different periods and I wanna check how much green and purple boxes there are in each row.

 

Thanks in advance for your attention,

José Gabriel

Hello Jamil,

 

I am having some issues when using the VBA codes you provided.

I have three types of cells I am looking to count.  All three have the same fill color (GREY) but different font colors(RED,PURPLE,BLUE).  Ultimately I would like to have a table that counts all similar fill colors (would include all three types).

 

It is difficult to explain the errors I'm getting so I included as much information in the attached excel as possible.

Hi Jose,

 

You were close, but your conditional formatting range area was different than what was used in the UDF. So, I modified the conditional formatting area range and used format painter to replicate it in other cells and it works now. I have attached the file here.

Hello Sylvia,

 

There is no need to overkill this with UDF.  We can simply use the built-in Excel formulas to achieve what you are trying to get.

 

This formula I wrote for you is maybe not easy ;) but it does the job and it is also dynamic, so if your year changes, it will change.   I mean the cell I2 for start of the period and AG29 the end of the period.

 

Please see attached file which I embedded the formulasin range AO20:AO23

 

For counting the PTO vacation days Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$B$3:$B$35,0)))

 

For counting Federal Holidays Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$12:$J$26,0)))

 

For counting Floating Holidays Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$30:$J$37,0)))

 

For counting the overall then Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$B$3:$B$35,0)))+SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$12:$J$26,0)))+SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$30:$J$37,0)))

 

Hope it helps.

 

Best regards,

Jamil

 

 

 

 

 

Jamil,

 

You are simply amazing.  One additional ask:

I want to count the unique "adjoining weekends" of all the vacation times I entered.  I couldn't figure out a "clean" way to do this so I just added additional columns of formulas that I will hide from view.

 

I used the same formula you provide to count these days but it doesn't count the unique days, but rather all the days in the designated range even though there are duplicates.

 

I attached the file so you could see my updates.  Hoping this is possible.

 

Thanks,

Sylvia

Hi Sylvia,

 

I have modified your helper column formulas and then used below formula in AP6 cell to count unique.

 

Please remember that this formula is array and when entering it, requires the special keystroke of Control+Shift+Enter

 

Please see attached file, the completed version.

 

=SUM(--(FREQUENCY(HolidayWeekends,HolidayWeekends)>0))+SUM(--(FREQUENCY(HolidayWeekends2,HolidayWeekends2)>0))+SUM(--(FREQUENCY(PTOW,PTOW)>0))+SUM(--(FREQUENCY(PTOW2,PTOW2)>0))+SUM(--(FREQUENCY(FloatingW,FloatingW)>0))+SUM(--(FREQUENCY(FloatingW2,FloatingW2)>0))

Hi Jamil!
Thanks for sharing code!

Can this code be modified so it works also with only one CF cell?
Currently it kind of works also with one cell, but only if function is refreshed by pressing "Enter" on particular cell, where UDF is located.

Hi Matiss,

 

The UDF already works with "only one CF cell"

 

attached is the workbook that demonstrates.