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
mecerrato
first try to check that your workbook calculation setting is "Automatic"
if it is automatic and still did not work then try putting the below line after the first line of UDF.

Application.Volatile

Hi Jamil,

 

I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)

 

Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)

 

I have attached a test sheet with the structure.

 

Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.

 

Thank you in advance,

Alin

Hi Jamil,

 

I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)

 

Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)

 

I have attached a test sheet with the structure.

 

Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.

 

Thank you in advance,

Alin

Hi Alin,

 

I checked one of your file and it is clear that you did not use the option of CF "- Use a formula to determine which cells to format"    the UDF only works if you used formula to determine which cells to format.

 

 

I have used one of the worksheets that i found on this topic, on which the count by color worked, and i adapted it for my example.

 

i have used a formula in conditional formatting in order to highlight all the cells that contain "TEXT".

 

But somehow it is showing only a count of three for all the three rows that i used for example.

 

I missed something for sure.

 

Attached the worksheet.

hi Alin,

 

I looked at your file.  The issue was that you had CF applied range A10 to A10:E12  while your UDF were using for each row.  the UDF range and the CF range should match, that is when it will work.

 

I have attached the workbook and also i have attached a recorded video on how to use the paintbrush to apply CF to the other cells without the hassle of recreating CF for each row.

thank you so much for this Jamil

I got it finally :)

 

I wish you all the best

 

 

Hi Jamil,

 

Please check my attached file which i want to count the colored CF cells by row. Problem is there are different formulas in each column, if i want to count the result by rows, i get an error. If i count by column wise it got results. Please help so i can count it by rows.

Thanks in advance.

Hi Roland,

If you read through my earlier posts. I have clearly mentioned that UDF only works if the conditional formating is set using Formula. you have used formulas and non-formula options to set your conditional formatting. that is the first issue that is causing the UDF not to work.

the second issue is that your are using the whole column references in your CF which is not going to work.

third issue is that your CF is set by column and you are trying to do the count in UDF using row. If you look at the example I illustrated in answer to Alin. you can see that i modified the CR using painbrush to reflect the rows.

so you need to fix the three of the issues i pointed, so that the UDF would work.

Hi Jamil,

The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.

Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.

I followed all the steps that you suggested (i hope i didn't missed something out).

I have attached the worksheet.

 

Thank you in advance

Alin

Good evening Mr. Jamil Mohammad,

 

 

I read your post and downloaded it. It works well.
But I would have a question of how to solve each line separately for each color.
Thank you for your reply and help in advance, a nice evening.

Example:

Hi Norbi,

 

I will respond to your post due to the fact that i had the same challenge as you, and Jamil showed me the way around this.

 

If you check the previous post in which Jamil replied to me you will see that the CF range need to be exactly the row for which you need to make the count. you cannot use a range like A3:G16 because it won't work. The correct way to do it is to set like this: A3:G3. - for the first row.

 

And afterwards you need to use the format painter and to paste the CF formulas row by row. As a tip, if you want to make multiple formats with the format painter you can double click the format painter and it will remain active for all the pastes.

 

Jamil also made a video with this step in the post he replied to me.

 

i have attached the modified workbook

 

i hope it helps

 

Alin

Hello Alin20

thank you very much for your answer managed to solve my problem. I realized that there were a lot of conditional formatting and could not count the colors correctly.

Thank you very much for your help again.

Hi,
norbi

Hi Jamil,

The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.

Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.

I followed all the steps that you suggested (i hope i didn't missed something out).

I have attached the worksheet.

 

Thank you in advance

Alin

Hi Alin_20.

 

You did not properly set your conditional formatting in your file, that is why it was not working.

 

I set the conditional formatting in the attached file correctly and it works now.

 

plz see attached.

When I use the formula, it works rows in the column except for the 2nd row of data.  It does not count that row.  Has anyone hit this issue or found a way around it?  It correctly counts all of the other rows so I know it is looking at the condition formatting.  

It should work. i suspect there must be an issue with the way CF range is set.
if you upload a dummy sample, i can take a look.

@Jamil Mohammad 

Good morning Jamil!  This thread has been SO helpful!  I really appreciate it.  Of course, since I'm joining I'm having trouble lol.

I added the UDF and made sure that the CF I'm using is based on formulas however I can't seem to get the results to be stable.

This is a fairly simple application and I'm feeling pretty dumb right now as I'm sure whatever it is I'm doing wrong is an easy fix.

Would you mind terribly taking a look?  I tried a couple different formulas in the CF but even the straight-forward ones don't always end in the correct result.  Sometimes the count is working and sometimes it isn't.

Any help you can give me would be much appreciated.  This is making me nuts lol.

 

Thanks!

 

@Laurafer 

 

thanks for your kind words. 

 

I saw your workbook and there are many issues with the CF formulas you set.

 

Double condition set for a single cell with contradicts each other. 

so example in the cell E28 there are two formulas in CF and both of them contradicts each other.

=IF(E28>D28,"true","pass")
=IF(E28<C28,"True","pass")

 

another issue is that you set IF formulas to return test in CF which is not correct way to set formula.

for example if you want a condition to turn red if certain condition is met then you can simply use equal sign and there is no need for IF function, in fact IF function ruins it.

 

for example if you want to turn cell E28 to red if its value is greater than D28 then you can simply use =E28>D28  simply as this. CF will return True if condition is met and false if it is not met.

 

Please see attached your workbook.

I have added two more UDFs so that you can debug and find the problem.

The first UDF return the formula used in the CF for that cell

 

Function CondFormulaformula(myCell, Optional cond As Long = 1) As String

CondFormulaformula = myCell.FormatConditions(cond).Formula1
End Function

 

this second formula evaluates the formula used in CF and returns the evaluated value.

Function CondFormula(myCell, Optional cond As Long = 1) As String

CondFormula = Application.Evaluate(myCell.FormatConditions(cond).Formula1)
End Function

 

you can see in the column H which i highlighted in yellow.  the evaluated CF from your formulas and they are not consistent.  with the help of two addtional UDFs you can debug your CF formulas and get  the correct result.

 

Hope this helps.

 

 

 

@Jamil Mohammad 

That worked perfectly!  Thank you so very very much!  One more question...Is there any way to have the cells that have no data in them remain uncolored?  I was playing with the formulas but as I'm sure you've figured out by now I'm not the best at them.  Everything else is working beautifully and I am grateful for what I've got but...  :)