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

[Deleted]

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!

[#2]

Hi, this kind of question is better communicated with a document, stating the problems in short form in the worksheet. Thanks for sharing...

[#3]

Hi,

This is the solution:

Reference

How to count and sum cells based on background color in Excel?

[#4 · Accepted solution]

I came across this post being unanswered. so here is a User Defined Function in VBA

to SUM 

use this UDF 

Function SumConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End Function

if you want to Count instead of SUM then use the below UDF

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

these solutions were provided to the similar question asked by other Excel users and worked for them.

For more detail here is the link for that answer 

[#5]

Thank you very much for you kind and very helpful answer.

I will try the explained method to finally resolve my issue.

Thanks and have a great day !

[#6]

You are most welcome. Thanks for the feedback.

[#7]

Hi Jamil,

Thank You for posting this solution, I hope that this will also solve my problem With counting the colored cells when using conditinal formatting.

However, so far I am not able to get a number Count, I only get "NO-COLOR". E.g. =CountConditionColorCells(E2;E2:E5) or =CountConditionColorCells(E2:E5;E2) Perhaps I'm completely far out... Could You please let me know how the formula should be written?

Regards,

Julie

[#8]

Hi Julie,

please see attached workbook sample that shows how you can use the UDF.

please post back, if you have any question.

[#9]

Hi Jamil,

Not sure why it is not working on my computer, because after pressing "edit" on Your document, it also got error value "#Name?", and You can see on my document Attached how it looks with error #VALUE! I wasn't able to attach the Excel file With Makro enabled, but have of course had the makro enabled file so far.

Great if you can advice.

Spoiler

[#10]

Hi Julie,

I checked your file.

it was not working because A) you need to enable macros in order to be able to use the UDF.

B)  the sample file you upload, you had only cells for criteria colored, but there wasn't any conditional formatting set in your workbook, that's why It did not work.

I have recorded a video that shows how you can set up your conditional formatting.

please download the attached video and watch it. also i have attached your workbook with conditional formatting set in it.

[#11]

Thank you so much Jamil!

It seemed like something was actually wrong with my original dropdown list, so took some time for me to understand why it didn't work. But now it is finally ok :)
All the best!

[#12]

You are welcome.

[#13]

I have tried to use the SUM formula here, but keep getting "NO COLOR"...I read through all your posts and tried to troubleshoot, but can't figure out what I'm doing wrong...can you take a look for me?

[#14]

Hi Michelle,

There is a longer thread on the same UDF, there are multiple things that can cause the return of "no color"

Please read the limitations of the UDF and other comments here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

[#15]

haii i already try this but not successful la.. please help me.

Please look at my attachment and my problem at sheet ZONE_WK25(CountColor) and at  column AV10 =countconditioncolorcells($D$10:$AS$10,AV5)

[#16]

null null

you asked the question in two places, so i replied here on why the UDF did not work for you.
please see my post here. https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

[#17]

the code doesnt work with my file..can you help me?

[#18]

Hi Haziyatul,

I checked your file. The range where the conditional formatting is applied and the range where the UDF uses have to be the same and they are not the same in your excel file.

CF uses $M$9:$EA$56 and UDF uses X9:Y13

so to make this work, you need to either exactly set CF for each of the vertical lines.
however, you would not need to use the UDF for this, you can simply achieve the same result by using the SUMIF or SUMPRODUCT formula. you can see the examples in my earlier posts in the same thread.

[#19]

Thank you Jamil. but this function doesn't work in case of "Top 10 Item" conditional formatting. Do you have any idea for my problem?

[#20]

Hi aa aaaa,

Please read my earlier messages, the UDF only works if the conditional formatting is set using formula.

[#21]

Bad luck!

Is there any solution for my problem?

[#22]

Open a new question not on this thread but a new question with sample dummy of your data.

[#23]

How can i open a new question?

[#24]

Please click here

at https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral

[#25]

Thank you very much.

[#26]

Hello.. can you solve the problem for attached file? 

I want to count the specific text colored by conditional formatting column wise. Conditional formatting done by formula.

[#27]

Please read my earlier messages on the thread and use the UDF to be able to count the conditionally formatted cells.

[#28]

okay

[#29]

you have a new question, so the experts would likely to answer your question over there.

[#30]

Hi Jamil,

I had a similar issue to Julie.

I did exactly what you said and the document worked.

Since saving, closing the document and today opening it the formula doesn't seem to work when calculating the colored cells.

I get the '#NAME' error.

Any assistance you can offer would be appreciated...?

Document attached.

[#31]

Hi Rowan,

You need to save as macro enabled workbook, when you save as normal workbook format the UDF disappears.

Use either macro enabled workbook format on save as it binary format. Then it will work.

You need to paste the code again into the workbook and save as then the Name error will fix.

[#32]

I have tried your formulas and they do not work.  I have been stuck on this for 3 weeks now and I'm trying to count my cells as they turn certain colors from Conditional Formatting rules I put in place for my drop down menu's.  If you would be able to assist me that would be wonderful because I am getting tired of watching and reading videos on how it wont work for me formulas.

Matthew

[#33]

Hi Mathew,

I looked at your file. You have set up the conditional formatting using the = cell value option .

The UDF only works if the conditional formatting is set using formulas not through the built-in option of conditional formatting.

You can read about it here
https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

What you are trying to do is possible, but you have set many conditional formatting rules.

I can help you if you give me the list of the text that are for green color and the list of the texts that are for red color.


these are all of the list and if you can give me which one of them are for green and which one for red. I can build the formula for you.

Acceptable Use Policy
Access Control List
Access Point
Address Resolution Protocol
Address Space Layout Randomization
Advanced Encryption Standard
Advanced Encryption Standard 256 bit
Advanced Persistent Threat
Annualized Loss Expectancy
Annualized Rate of Occurrence
Anti-virus
Application Programming Interface
Application Service Provider
Attribute-based Access Control
Authentication Header
Authentication-Authorization-Accounting
Basic Input/Output System
Bridge Protocol Data Unit
Bring Your Own Device
Business Availability Center
Business Continuity Planning
Business Impact Analysis
Business Partners Agreement
Certificate
Certificate Authority
Challenge Handshake Authentication Protocol
Chief Information Officer
Cipher Block Chaining
Cipher Feedback
Closed-Circuit TeleVision
Common Access Card
Completely Automated Public Turing Test to Tell Computers and Humans Apart
Computer Emergency Response Team
Computer Incident Response Team
Content Management System
Contingency Planning
Continuity of Operations Plan
Controller Area Network
Corporate Owned-Personally Enabled
Corrective Action Report
Counter-Mode/CBC-Mac Protocol
Cyclical Redundancy Check
Triple Digital Encryption Standard

[#34]

These are all the acronyms I am trying to achieve on my spreadsheet, thank you for your assistance

 Matthew

[#35]

Hi Mathew,

I did not understand your question. The attachment is list of acronyms in pdf.
your Excel file which you posted earlier does not contain of all of these.
you question was related to counting the green and red and to make this work, you need to set up the conditional formatting by yourself. then you can give me the list of the acronyms that are in green category and also the list which are in red category. by then i will be able to give you a formula that handles that count for red and green. Right now, I have not received sufficient information related to your excel problem.

[#36]

Hi Jamil

I stumbled upon your code as I am using conditional formatting formula to highlights my cell. Now I want to count the highlighted cells.

Similar to Julie, the VBA returns "no color" instead of the numbers. I have done everything that I can possibly can. 

I suspect it doesn't work because I use this formula in my conditional formatting =if(isblank($K$1),0,(search($K$1,A5))). A5 is where my data starts. K1 is where when someone type a text and that text is in the data, it will highlight those texts. Attached is an example (which didn't quite work but you'll get the idea).

Thanks for your help. 

[#37]

hi 7 Heven,

You have used Search function and also whole column reference A:A which is not compatible with the UDF.   I have modified the formula and range in the attached example and the UDF works.

[#38]

Hi Jamil

I can't believe my luck that you actually replied to my query! Thank you very much!

If I modified the formula as per your suggestion and remove the search function, it doesn't work on my master spreadsheet. Is there a way to modify the UDF to make it work with search function? 

Many thanks again.

[#39]

Hi Jamil I was able to successfully use your code to count my conditionally formatted cells but my data changes regularly and the function doesn't automatically refresh. In order for it to pick up the changes i have to refresh the cell with F2 and then enter, any solution for that?

[#40]

7_Heaven,
you can use search function, but your ranges should not be whole column reference. I have shown in the example in my previous post how you can use it.

[#41]

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

[#42]

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

[#43]

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

[#44]

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.

[#45]

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.

[#46]

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.

[#47]

thank you so much for this Jamil

I got it finally :)

I wish you all the best

[#48]

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.

[#49]

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.

[#50]

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