- last edited on
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
09-19-2018 02:37 PM
09-20-2018 04:21 AM
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.
09-20-2018 05:20 AM
Thanks for the feedback Matt. Glad to hear you figured it out by yourself.
09-27-2018 06:52 AM
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!
09-27-2018 06:59 AM
you can simply do that with SUMIF function.
I have embedded the formula into the attached workbook.
09-27-2018 07:05 AM
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!
10-06-2018 08:16 PM
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.
10-08-2018 11:58 AM
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.
10-08-2018 09:10 PM
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.
10-08-2018 10:17 PM
10-09-2018 07:01 PM
Got it. Thanks a lot, Jamil. Again, really appreciate all your help and quick responses.
10-11-2018 01:48 PM
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,
10-20-2018 06:00 AM
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.
10-23-2018 08:25 AM
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.
10-23-2018 09:03 AM
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.
10-25-2018 06:53 PM
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.
10-27-2018 04:05 PM
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.
11-01-2018 02:17 AM
11-01-2018 05:25 AM - edited 11-04-2018 03:47 PM
The UDF already works with "only one CF cell"
attached is the workbook that demonstrates.
by Pau_Me on September 19, 2019
by matt nipper on December 20, 2016