

How to conditionally format cells, and then have it count how many cells were colored.

I have my sheet set up to conditionally format cells with a color based on the text contained within the cell, but I need a way to count all cells with each color, so I can know how many cells are for example red, or blue.

Is there any easy way to do this?

It's essentially the same as me doing CTR+F to find certain text, color it, and then count it, but the volume is so large that I couldn't count it by hand without it taking ages.

Any help is immensely appreciated.

60% Upvoted
This thread is archived
New comments cannot be posted and votes cannot be cast
level 1

Whatever formula you're using for your conditional formatting should also be usable with something like COUNTIF(S) or SUMPRODUCT to provide the count - can you post a sample workbook?

level 2

This is the best option, if you are using a formula to determine which cells are being coloured you can use the same formula within a count function to count the formatted cells.

level 1
Original Poster1 point · 5 years ago

When performing a google search on this subject, many solutions include using a VBA code inserted "into your module", if anyone could help me understand where I would enter the appropriate code at in excel it would also be greatly appreciated, thank you.

level 2
1 point · 5 years ago

Press ALT+F11 and you will open up the Visual Basic programming editor. In here you find a small tree with your filename on the top left corner.

Right click the file then select add module and a module will appear named Module1, and the code window to the right. Paste your code here. To run that specific code, put the cursor inside the code and select the play button in the toolbar. (Or for user defined formulas/UDF:s write the formula in the normal sheet)

Welcome to programing =)

...oh, and you have to select a different "macro-enabled" format when saving, select "XLSM" as the file format otherwise the code will disappear...

That said; the correct solution here is probably to use a =countif() workbook function with the same formula as the conditional formatting!

level 1

VBA is a programming language which works on MS Office elements. There's a lot you can do with it, but more often than not you should try doing it with existing formulas or excel functionality. This is the case this time.

You should try with the Conditional Formatting option (in Excel 2007, 10 or 13, it's right there in the Home tab). The formula you want to use is probably FIND or SEARCH, inside an ISNUMBER. =ISNUMBER(FIND(...)) To count, I would add again that ISNUMBER(FIND(...)) formula in a new column, and count those up.


137k Spreadsheet Warriors285 Formulas being written
Discuss and answer questions about Microsoft Office Excel
Top Posts From The r/excel Community
Continue browsing in r/excel
Community Details


Spreadsheet Warriors


Formulas being written

Discuss and answer questions about Microsoft Office Excel

r/excel Rules
Post titles must be specific to your problem
Provide specific examples in your post
Use the appropriate flair for non-questions
Resources to learn Excel or VBA



OPs can reply to any solutions with:

Solution Verified

This will change the flair to SOLVED and award the user a ClippyPoint. Please be patient, sometimes it takes a couple of minutes.


Include a screenshot, use the table feature of the reddit text editor, or use the ExcelToReddit table converter to present your data. Courtesy of u/tirlibibi17

Recent ClippyPoint Milestones!

Congratulations and thank you to these contributors

Date User CP
2019.04.19 canyoucometoday 25
2019.04.18 Antimutt 800
2019.04.17 lucasartss 10
2019.04.17 thermodynamic_scribe 10
2019.04.16 Riovas 25
2019.04.16 excelevator 1500
2019.04.15 ElusiveTurtle 10
2019.04.14 bosco_yip 10
2019.04.13 mh_mike 100
2019.04.13 WorkSpeed 10
2019.04.12 pancak3d 900
2019.04.11 ryanmuckel 10
2019.04.10 lessthanleo 25
2019.04.10 ValueBasedPugs 100
2019.04.08 daishiknyte 10


A community since March 11, 2009