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