Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range B2:C11, column C has Conditional Formatting applied based on the following CF formula.
What's on this page
- How to apply Conditonal Formatting
- How to count cells based on conditionally formatted background color [Excel 365]
- How to count cells based on conditionally formatted background color [Previous Excel versions]
- Count cells based on conditionally formatted background color using Filter tool and SUBTOTAL function [Excel 365]
- Get excel *.xlsx file
A Conditional Formatting formula allows you to create your own condition or criteria if the built-in conditions are not enough.
As far as I know, you can only count cells with cell background color, not font color or bold/italic etc. It is also not possible to count cells highlighted with Conditional Formatting using VBA code, however, you can count cells manually formatted using VBA code.
If you know how to apply Conditional Formatting based on a formula you can skip these steps and go to the next section right now. Here are the steps to apply Conditional Formatting to a cell range.
How to apply Conditional Formatting
These steps explain how to apply Conditional Formatting to a cell range based on a formula.
- Select the cell range you want to highlight.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on "Conditional Formatting" button on the ribbon.
- Press with left mouse button on "New Rule...", a dialog box appears. See the image above.
- Type your formula in "Format values where this formula is true:".
- Press with left mouse button on "Format..." button and another dialog box appears.
- Press with left mouse button on tab "Fill" on the top menu.
- Pick a color.
- Press with left mouse button on "OK" button.
- Press with left mouse button on "OK" button again.
How to count cells with a specific cell background color [Excel 365]
These steps show how to count highlighted cells.
- Select any cell in the data set.
- Press CTRL + T to open "Create Table" dialog box.
- Press with left mouse button on the checkbox accordingly based on the layout of your data set.
- Press with left mouse button on "OK" button to create the Excel Table.
- Press with left mouse button on the arrow next to the column name you want count cells in.
- A pop-up menu appears, press with left mouse button on "Filter by Color". Another pop-up menu appears, press with left mouse button on the color you want to sort by.
- The Excel Table now shows only the cells with the selected background color.
- Select any cell in the Excel Table and a new tab on the ribbon shows up named "Table Design", press with left mouse button on that tab to select it.
- Press with left mouse button on the checkbox "Total Row" located on the ribbon, see image above. A new row appears below the Excel Table values.
- Press with left mouse button on the number next to total. An arrow appears next to the number, press with left mouse button on that arrow. See image above.
- Press with left mouse button on "Count".
The number of cells highlighted with a given cell background color using conditional formatting is shown in cell C12.
How to count cells with a specific cell color [Previous Excel versions]
- Press with right mouse button on on a cell that has a background color you want to count. A pop-up menu appears.
- Press with left mouse button on "Sort" and another pop-up menu shows up.
- Press with mouse on "Put Selected Cell Color On Top".
- Select all colored cells.
- Excel returns the count of your selection in the lower right corner of your Excel window. See image above.
Count cells with a specific cell background color using the FILTER tool and the SUBTOTAL function [Excel 365]
This section demonstrates how to count cell background color based on Conditional Formatting using the Filter tool. The Filter tool is built-in to Excel, the following steps show you how.
- Select any cell in your data set.
- Press shortcut keys CTRL + SHIFT + L to apply the Filter feature to your data set. You know it is there when you have arrows next to your column names. You can also go to tab "Data" on the ribbon and press with left mouse button on "Filter" button to apply it.
- Press with left mouse button on the arrow in the column you want to count a specific cell background color. A pop-up menu appears.
- Press with mouse on "Filter by Color" and another pop-up menu appears.
- Press with mouse on the color you want to filter by, see image above.
The following formula will count visible cells in cell range C3:C11 that is not empty. I entered it in cell C13.
The SUBTOTAL function allows you to perform many different calculations based on the first argument. It is also able to perform these calculations to filtered values in contrast to the regular SUM, AVERAGE, COUNT and COUNTA functions.
SUBTOTAL(function_num, ref1, ...)
Recommended reading
- Conditional Formatting (Excel easy)
- Use conditional formatting to highlight information (Microsoft Ofiice)
- Using If/Then in Conditional Formatting in Excel
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Find earliest and latest overlapping dates in a set of date ranges based on a condition
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
The DSUM function adds numbers in a database that meets a condition or criteria. Formula in cell B15: =DSUM(B5:D12, 3, B2:D3) […]
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
Use slicers to quickly filter chart data
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
How to use the FORECAST function
The FORECAST function calculates a value based on existing x and y values using linear regression. Use this function to […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
29 Responses to “Count Conditionally Formatted cells”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
Really brilliant UDF fella! one thing tho...
I have used it for a spreadsheet of mine and have coune accross a problem. When using the code to count the colour red its is also counting cells that are blank (colour wise) as red? I have got around it using another formular but wondered if there was anything you cound do?
thanks
James,
I don´understand, can you upload an example file?
This is a really awesome tutorial and MUCH needed function!
I wonder if there is a way for it to work in Excel 2013? I tried it with a basic color and with Excel's Conditional Formatting, and no luck with either, unfortunately.
Andrew,
There is something wrong with the 2010 version also.
Oh! So, does that mean you are working on it, or have you abandoned this function? It would be really awesome and worth pursuing. So many people could use this.
Anyway the tutorial is well done, so if the VBA code gets fixed, please do post an update! Should get many website hits for you because its such a useful analysis.
Thanks.
I am trying to do this with multiple columns.
Example:
I have an employee schedule that will highlight a color per half hour if it is between the employee schedule.
Rows: Employee's
Columns: Time by half hour
I want to count how many employees are working during each half hour by counting the highlighted cell. This formula works for one cell but then i get errors with others. it says "Formula emits adjacent cells"
Any idea what that means?
I try it same, but it got output as 0.
And i check with different cells.
Can any one help me.
Note: If i select empty cell i get color not found.
Same here, I got "color not found even after changing the color to match the picture.
I have found that the script only works for one conditional formatting rule at a time. If you select a group of cells that have different rules, the script only gives the result of the first rule. Does anyone know how to make it work with more than one rule?
Hi
This works great but for two cells or more, but doesn't work for a single cell. It always says 0 regardless. Is there an easy change to make this work for one cell i.e. does this cell have the conditional formatting active?
Brett,
The macro is not working as I thought it would and I don´t know how to fix it.
Hi Oscar and Brett,
Your code is just fine. I used conditional formatting to color code a matrix (imagine a dashboard with just colors in empty cells) and used your code to count the # of a particular color in a column. It works!! yay!! Just wanted to let you know.
good day
I added some more numbers in the column and colored another several cells ,
on d2 I changed =countcfcells(a2:a17,c2) to =countcfcells(a2:a24,c2)
then I received #value!
only the first quantity works.
a2:a17
and not a2:a24
I try to understand.
for several hours -no success.
can you help ?
thanks
yosef
This is much more succinct than Chip Pearson's monster code. However, it does not work for Excel 2013. Even your workbook, as soon as it's opened the 10 changes to #VALUE!
Furthermore it is unclear how you colored the blank cell - did you simply fill with a background of red? Don't think so but you did not explain. Sorry we aren't mind readers - smile. If you did not, how did you conditionally format that blank cell to be red? I tried various ways and none worked. This is frustrating as hell not to be able to simply count the colored cells from CF.
Thanks for any assistance,
Mort in Dallas
Hi mate, I am trying to do the Conditional format colour count and I have pasted and tried to use the function. But when I run the function it says that the colour was not found. Can you please help me with that.
okey i had used this code it gives me a error
Compile Erorr :
Syntax error:
(Module 1 5:0)
Function CountCFCells(rng As Range, C As Range)
Dim i As Single, j As Long, k As Long
Dim chk As Boolean, Str1 As String, CFCELL As Range
chk = False
For i = 1 To rng.FormatConditions.Count
If rng.FormatConditions(i).Interior.ColorIndex = C.Interior.ColorIndex Then
chk = True
Exit For
End If
Next i
j = 0
k = 0
If chk = True Then
For Each CFCELL In rng
Str1 = CFCELL.FormatConditions(i).Formula1
Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1)
Str1 = Application.ConvertFormula(Str1, xlR1C1, xlA1, , ActiveCell.Resize(rng.Rows.Count, rng.Columns.Count).Cells(k + 1))
If Evaluate(Str1) = True Then j = j + 1
k = k + 1
Next CFCELL
Else
CountCFCells = "Color not found"
Exit Function
End If
CountCFCells = j
End Function
The format can count only greater than or less than conditional format only. can't count between condition format.
EX.
Red colour ratio = 1 to 7
Yellow colour ratio = 8 to 11
Green colour ratio = 12 to 15
Blue colour ratio = above 15
Do you know of anyway to count up only the visable rows? I have some filtered data and I need to just get the visable rows, do a count of each color, and get the average.
Hello, the formula works perfectly if conditional formatting is "cell is greater than".
But when you use a formula like this in conditional formatting: "=if(A1=1,true,false) the formula countofcells returns "#VALUE!"
Any ideas how to make it work with more complex conditional formatings ???
Hi,
This works perfectly in columns, but what part do I have to change in VBA to count in rows?
Thanks in advance for your advice.
I have the same question as Steven, the columns work great, but I can't get it to work on rows. What do I need to do.
Thanks
Tommy and Steven,
Don't use the macro in this post, it is not working.
I recommend you tweak and use your conditional formatting formula to count conditionally formatted cells.
Oscar
Thanks for the quick reply, but when I do the same thing you sent me it doesn't work. How can I send you my file so you can see what i'm trying to do. I don't understand how to tweak my conditional formatting formula to count the cells.
Thanks again
Oscar, your code is currently working when I opened the file and I think it will continue to work on the file that I need it to. However, I need to adjust the code to count rows not columns, what portion would I modify in order to do so?
Thanks in Advance
Shan
The code is not reliable, I recommend you don't use it.
Hi Oscar,
I tried to change the conditional formatting ie:format only cells that contain(cell value = 0) and the result is zero though the red cells are 6.
Please help.
Thanks you
rechel
Yes, the macro is not working. Do not use it.
Hello, I am trying to figure out how to return a value if a cell is highlighted a colour.
I.E If I have a spreadsheet and I highlight a cell red which contains the word 'apple' is there a way to return this value?
Hi,
Do you know if there is a way to count conditionally colored cells across a specific row?
Thanks
Rachel