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 category
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 […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Count values category
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 […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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