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]

@Johnnyb7277 

you are probably manually adding the conditional formatting on each column which can be time consuming.   you can avoid that, by using the pain brush option of Excel.

select the single column that has the CF and then double click on the Excel paint brush and then now click each of the other columns that you want the CF applied. this way the exact CF will apply without the hassle of manual creation of CF.

[#3]

@Jamil Mohammad 

You're 100% correct, adding them manually and they are time consuming! 

A few issues - and I added a small sample excel sample of what I am talking about to make it easier to understand my issues.

1. When I remove a row from the sheet (and it is not at the bottom of all the rows) all the CF is combined across the columns and then negates the CountConditionalCells UDF. 

 Possible solution:

   A. Add a "closed" note to the row but the file is SLOOOW as is based on all the CF this will just keep a lot of extra CF cells I don't need to track or care about.

   B. Push the "to be deleted" row to the bottom and delete, but this is not robust and many teams are adopting this file and someone will mess that up.

2. When I add a row to the sheet it only has the CF for that specific row and does not automatically roll the CF into that row. Which is then another manual process to update the CF to capture that row even if with a paint brush method.

3. The entire sheet is based on dates that are auto populated based on previous up stream deliverables and standard lead times to complete the tasks. The only way I have found to mark a cell (deliverable) as complete (and to save the date for scheduling and historical purposes) is to have the team member go in and manually change the color to blue which triggers a CF based on that color to change the font white. So I am unable to do a paint brush option to all the columns even one at a time as there are some that have manually changed backgrounds because the deliverable was completed and the date stored for deriving the next step or historical purposes.

Again, you're the man with this stuff and all the help you have given so far has been amazing and I really appreciate it. Hope you and your family are doing well during the COVID-19 Impact and stay safe Jamil.

[#4]

@Jamil Mohammad Hello Jamil!

I am trying to get the SUMBYCOLOR function working with conditional formatting but I don't know why it's not working. I took exemple with the file you provided in the first page, copied the VBA module into my file etc.. but I'm getting 0 as result :(

Could you please help me out with this ?

I attached my file so you can see. Many thanks for your help!!

[#5]

@Johnnyb7277 

Thanks for your message and kind words.  

I guess when you involve the manual background changing color to blue, then it will have to be the way that you have already adopted. I am sure there are better ways to make it robust, but this is as far as I could go. Thank you for your understanding.

[#6]

@Skober92 

Hi,  I looked at your file.   the reason it did not work was because you had set the conditional formatting wrong.   you had two overlapping conditions.   you had =C4=$A$3 and you had another one =C4>$A$3

.  I combined both of them as =C4>=$A$3 and then the UDF returned correct value. Plz check the attached file.

[#7]

WoW thanks a lot!! You rock

[#8]

@Jamil Mohammad First thank you for this effort. Second I try the Function you gave to count the formatted colored cells, it is working only for one row and I need to pull down it always giving the reading of first row. Please Can you help with this to let this Function working as I pull down to the next rows.

[#9]

Please see my answer which I replied to Kalpesh64 link here https://techcommunity.microsoft.com/t5/excel/how-to-count-and-sum-quot-condtional-formatting-quot-ce...



There I have shown how you can do it per row. file is attached there and also the GIF animated recorded video.

[#10]

@Jamil Mohammad 

Jamil,

Thanks for helping, and sometimes doing it almost at light speed.  You have helped me in the past.

I have a similar problem, but instead of counting, I just need to show on the screen by conditional formatting which numbers match the criteria.

How can I use conditional formatting to highlight all the numbers in a row if they go in ascending order across columns, and skipping the blank cells.

Also, which formula do I use to show next to the beginning (or end, or someplace) of the row a "YES" if the criteria is met?

I attached an example. I repeated the tables. The fist table at the top is where the CF or formula is needed. The bottom part, the repetition is where I illustrate what I need, how it needs to look.

Thank you so much for helping.

[#11]

@Jamil Mohammad 

@Jamil Mohammad 

Hi Jamil. Thank you so much for this thread. I don't know if it's still active, but I'm hoping that you'll be able to help me.

I tried using your attached example file and added a third color, which doesn't work for me. I did conditional formatting using formulas, but with an AND statement. Can you have a look at my file and tell me if I made an error, or if the VBA code doesn't allow for "more advanced" formula statements?

BR Esben

[#12]

@Z Z 

Hi,  albeit your question is not related to the topic in this thread, I will try to give you an answer.

there is an easy way to check if a range is sorted sorted.  It is quite easy, even if you have a single blank in your data.  Examples https://exceloffthegrid.com/excel-formula-to-check-if-a-list-is-sorted/ 

However, with the data you presented, it is much more complicated as the data is scattered with multiple blanks in between them and sometimes there is only a single value or duplicate values which makes it difficult to find a pattern for the formula. 

Perhaps you can work out using some helper columns to clean the data before running the logical test. 

[#13]

@Esbenaam 

The UDF did what it supposed to do.  It did not work in the example you shared because when you set the third condition in red color=AND(A3>50,A3<60)     this was already overlapping with the green condition, meaning that green was already counting anything above 50 . Therefore, you had a wrong count on the green when I changed the green to >=60 then it worked.  see in the attached example.

[#14]

@Jamil Mohammad 

Hi Jamil

Thank you for the quick reply! Something seems to by off with my computer/excel.

When I first open the example file you sent, I am able to see the formula working as intended. However, after enabling editing, the formula stops working and gives me the "#VALUE!" error message - as shown in the second picture. Do you have an explanation for this? Could it be that I'm using excel in a different language? (Danish)

BR Esben

[#15]

@Esbenaam 

Hej,  It has nothing to do excel language being in Danish. 

Can you try the attached version?  if you still get Value error. try to press F9 and see if what happens.

if in all cases, it returns Value Error. see if you have any macros in PERSONAL.XLSB excel start up folder. 

also, some add-ins does not let the UDF to work properly. so if you have any add-in activated, try to deactivate it temporary and check the file.

[#16]

Hi @Jamil Mohammad 

I tried opening the excel file on my personal laptop and it worked fine, however it still doesn't work on my work laptop. On my work laptop I do not have any add-inns activated and my XLSTART folder is empty. Pressing F9 just makes the VALUE! flash and nothing happens. 

My personal computer uses Microsoft 365 for enterprise (excel version 2004), and my work computer uses Microsoft Office 365 ProPlus (excel version 1908) - could this have something to do with the error?

BR Esben

[#17]

@Esbenaam 

Hi,  

No, it has nothing to do with version of Excel. I tried it both with the enterprise version exactly as you have, as well as the 365 version. both worked for me.  initially It 365 it shows Value error but click one of the cells with formula press F2 to enter in edit mode and enter. it should return the correct value.

please try it in the attached version.

[#18]

@Michael Collins 

I need to count by font color by conditional formatting. Pls advice how to do?

attached is the file.

Count how many cell above 7, below 3 and between 3-7

[#19]

Hi Jamil,

I have used your code on the first page, works wonderfully.

However, my cells are formatted using conditional formatting 3 times, changing colour each time. Is there a way to count the 'current colour' only? For example, the top box should be 9 but it's counting the first colour only.

Red	16
Orange	1
Green	7

Thank you.

[#20]

@lihkiong 

Hi,  you did not set your conditional formattings using formula. that is why it does not work.

please see an example file with the code for counting based on the font color set by conditional formatting in my answer here https://techcommunity.microsoft.com/t5/excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

[#21]

@jvass92 

It will work as long as you do not have overlapping conditional formatted rules.

Please check if your rules are overlapping. you can upload an example file and I can look at it.