SOLVED

How to count and sum "Condtional formatting" cells by color in Excel 2010?

Deleted
Not applicable

I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.

 

Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )

 
My question is: Is there any functional code (or other aproach) which can count and sum all cells by "Conditional formatted" colors, on one sheet, and that the generated result is linked through common formulas? That can be updated/refreshed/code run during the work process as a complete overview of colors of a matrix (and not by picking every color every time as I mentioned).
 
Thanks in advance!

 
181 Replies
@Clint00

I checked your file, none of the Functions which you used in the workbook is written by me. You should use the UDF which I wrote, you can find it in the same thread.

@Jamil Mohammad 

 

I tried this code, but it only works for the first row. It doesn't work on the rest of the rows. What am I doing wrong? Please see attached.

@Kalpesh64 

 

Hi,   It did not work because, the UDF is range sensitive.  the range set in Conditional formatting and the range used int he UDF must match.   Your UDF ranges were looking to for each row and the CF was set for the entire range. I changed the CF range to single row, then used paint brush to create same CF for each row and then the UDF calculation started to work.

I have attached the workbook, as well as a animated video on how did I do it.

 

@Jamil Mohammad How to use this function to row. I need to pull the formula and count the co;ored formatted cells in each row

@khaled170 

 

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. 

@Jamil Mohammad Thank you but can you show me the code which you use to get this formula

@khaled170 

 

Code is in the attached workbook.

@Jamil Mohammad Dear Mr. Jamil : I tried to use on my sheet but it give me #VALUE!, I attached the sheet. I used 2 formula ( in Cell AN2 and AN5 )for Formatting the cells . Please can you help me get the formula and show me the code.

@Jamil Mohammad I'm attaching a TEST file. Please direct your attention to cells: J5:J8, Column 'T', Column 'U', cells 'Z13:Z15' and 'AA13:AA15'. 

 

What I'm trying to achieve is for UDF to calculate/ COUNT the perspective colored cells in Column U accordingly; then report that number in Cells AA13, AA14 and AA15.  I then will copy/link that number or cell to my LEGEND located at Cell: R62 - R65.  Column U is the Color Indicator Column. Obviously the color will change based on the average time per student. I want to count those results appropriately to automatically convert a Weekly Percentage.  After applying your code, I still can't get the code to talk properly within the document.  Please Help!

@khaled170 

 

Perhaps, you did not read my earlier posts. I have mentioned that the range used in the UDF must be a mirror of the range in the conditional formatting.  I can see from your example file that you have applied multiple range in the single rule of conditional formatting  and also it is vertical by column and yet the UDF is using single range of single horizontal row. so the ranges do not match that is why you get the error.

@Lonnie White III

 

Hi,  I looked at your file.  there are many overlapping conditional formatting rules which you have set. for example the color yellow is overrulled by color red. so the true count of the yellow cells are distorted.

 

Perhaps, you are overkilling this by using this advanced udf for such calculation that can be done using built-in array formulas.  for example you count of yellow cells can be achieved using the =SUMPRODUCT((--(T4:T55<>""))*(T4:T55<J6)*(T4:T55>J7))   the first part of the array excludes blank cells and second part of array is exactly as per your conditional formatting and the third part of array is not to overrule the second part. as it is now in the conditional formatting.  try to calculate it using the built in formula as i have given example for one of the items in the list.

 

 

@Jamil Mohammad I already get the code running on my log sheet , but I am getting #VALUE! then If I select the formula and press Enter it will run and if I drag it below it will give correct answer only for 10 rows below and below this the formula showing #VALUE! then need from the formula in the last row which showing correct result to drag below and again it will give correct answer in the below 10 rows and the other row will show #VALUE!.

Is their any way to let the formula properly work for all the rows as I drag it below ?

Attached the file I am working with.

@Jamil Mohammad Thanks for the quick reply.  I implemented the formula you provide and it returned a number higher than what was expected. All I did was paste the formula in a blank cell and it returned "29" as a result. Let me know if I need to reorder my CF rules or try another strategy.  How do you feel about perhaps counting by color code?  Will this emit the same issue?

 

Thanks Jamil!!

 

LONNIE

Dear Mr. Jamil : I have a question regarding the update of the link of one formula in one workbook is not updating unless I will open the other work book.I try enable automatic link updates in Excel 2013 by selecting File, Options, Trust Center, Trust Center Settings, External Content, and under the section labeled Security settings for Workbook Links, select Enable automatic update for all Workbook Links, and then click OK. But nothing change. Please can you help.

@Lonnie White III 

 

In your excel file, you had a pre set value of 20 for yellow color (Below 30 minutes; more Login Time needed.)  I placed the formula =SUMPRODUCT((--(T4:T55<>""))*(T4:T55<J6)*(T4:T55>J7)) there and it returned 20.   similarly, for other conditions, you can specify what should be the rule to count and using SUMPRODUCT function you can easily get the correct count. 

 

Please see attached workbook the example of yellow color is shown with formula there in cell R63

@khaled170 

 

I run a small test and I see that there are a lots of circular references in formulas inside conditional formatting rules. and each conditional formatting rules has multiple logical test and it overwhelm the calculation engine.  Unfortunately, I do not have time to build from scratch a neat table for you, as it takes a lot of time.  If I was you, I would not use nested IF inside the Conditional formatting rules and instead use helper column, this way you avoid too much calculation and circular reference issues. 

 

@khaled170 

 

I assume this is not question related to captioned title of this thread. So, I wouldn't know why it does not work. As i have not seen the file. try to see if you have any broken link among the links. It could be the issue similar to the issue report here

 

https://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/excel-2013-external-links...

 

see the answer from Ashish.

@Jamil Mohammad 

Hi Jamil, thanks for your quick response.

 

I am trying to count the cells in column P in each different section and total these to a specific cell- these have been condition formatted to change colour depending on the values that are put into them, which are summed by the Moulds & Yeasts Columns (Q&R)- for example, I want the total of green cells within P9-18 to be totalled inside C51 and then the orange and red cells from P9-18 in C52 and C53, but I want C51, 52, and 53 to be able to update when changes occur in P9-18 and the colours change. I have managed to add a VBA code where the totals can recognise the colours but I cannot get them to update when the colours change from their value.

I have attached a copy of the workbook without the macro enabled as it was the only way the site would allow me to send it. hope this helps.

Many thanks for your help!

Kind regards,
Chris

@ChrisDempster 

 

Hi Chris,

 

What you are after is possible and this UDF can do that.

 

If you read my earlier messages in this thread, you will notice that I mentioned that for this UDF to work, the conditional formatting rule should be creating using the option "use a formula to determine which cell to format"  

 

to demonstrate, I have changed the rules of conditional formatting for range P9-18 and replaced it with formula. I replaced the rule, between 0 to 9 with =AND(ISNUMBER(P9),LEN(P9)=1) and also the other two colors as you can see it in the attached file.

 

then I placed the UDF as an example in the cell C51 which correctly returns the value.  given this example, you can replicate this for other ranges/cells.

 

plz see attached file.