SOLVED

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

Highlighted
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!

 
147 Replies
Highlighted

Bad luck!

Is there any solution for my problem?

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

How can i open a new question?

Highlighted
Highlighted

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.

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

okay

Highlighted

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

Highlighted

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.

Highlighted
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.
Highlighted

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

Highlighted
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
Highlighted

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

 

 Matthew

Highlighted
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.
Highlighted

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. 

Highlighted

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.

 

Highlighted

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.

Highlighted

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?

Highlighted
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.