Count cells based on background color
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined functions (UDF) that will count cell values based on font color, cell color and bold/italic.
What's on this page
- How to change cell background color
- How to count cells with a specific background color (Excel 365)
- How to count cells with a specific background color (Previous Excel versions)
- How to count cells based on criteria font color, cell color, and bold/italic (VBA)
- How to count unique distinct cell values based on criteria font color, cell color, and bold/italic (VBA)
- Get Excel *.xlsm file
How to change cell background color
Excel lets you color cells using the "Fill Color" tool located on tab "Home" on the ribbon.
- Select the cell or cell range you want to apply a different background color to.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on "Fill Color" button and a pop-up menu appears.
- Pick a color.
Sometimes it can be useful to know the number of cells filled with a specific background color. Excel 365 makes this really easy, there is a work-around for older Excel versions that I will show in this article as well.
How to count cells with a specific background-color [Excel 365]
The Excel Table allows you to filter and count cells with a specific background color.
- Select any cell in the data set.
- Press shortcut keys CTRL + T to show the "Create Table" dialog box.
- Enable the checkbox if your data has header names.
- Press with left mouse button on the "OK" button to convert cell range to an Excel Table.
- Press with left mouse button on the arrow next to the header name which corresponds to the column you want to filter.
- 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 filter by.
- Select any cell in the Excel Table. A new tab shows up on the ribbon named "Table Design".
- Press with mouse on the tab "Table Design" on the ribbon.
- Press with mouse on checkbox "Total Row", see image above.
A new cell appears below the Excel Table, see image above.
- Select the new cell. An arrow appears next to the number, see image above.
- Press with left mouse button on the "Arrow", a pop-up menu appears.
- Press with left mouse button on "Count".
The number in the cell changes, it now shows the total count of cells based on the filter we applied. The formula bars hows this formula:
The SUBTOTAL function is able to count visible cells in a filtered table, number 103 stands for COUNT and [Numbers] is a structured reference to data in column Numbers.
Structured references appear automatically when you reference data in an Excel Table, in this case, Excel created this for us when we enabled checkbox "Total row" in a previous step.
How to count cells with a specific background-color [Previous Excel versions]
- Press with right mouse button on on a cell that has the background color you want to filter by. A pop-up menu appears.
- Press with mouse on "Sort", another pop-up menu shows up.
- Press with left mouse button on "Put selected Cell color on top".
The data is now sorted by cell color, see image above. Select all cells with the background color you want to count, they should now be adjacent to each other and sorted on top.
Excel counts the selected cells for and shows the number in the lower right corner of your Excel window.
Count cells by cell font properties (font color, cell color, bold or italic)
The image above demonstrates a User defined function (UDF) in cell D3 that counts cells in column A based on the cell formatting in cell C3. The font properties are color, cell color and bold/italic.
How difficult would it be to make it count colour alone (so not unique values) and/or use cell font colour or other features also (bold for example)?Excel has some quick green (with green text) and red (with red text) preset formats that people often use in either conditional formats or by manual application and although the unique values is very useful in a lot of cases this is even more detailed than required for just a count of the formatted conditions.Awesome site by the way, it's been invaluable to me lately!
Cheers.
Custom function in cell D3:
The user defined function counts how many times the font properties in cell C3 match each cell in the cell range A1:A20.
User defined function
'Name UDF and dimension parameters and declare their data types
Function CountC(rng As Range, Cell As Range)
'Dimension variables and declare data types
Dim CellC As Range, ucoll As New Collection, i As Single
'Save 0 (zero) to variable i
i = 0
'Iterate through each cell in parameter rng using variable CellC
For Each CellC In rng
'Check if cell background color matches parameter Cell's cell background color
If CellC.Interior.Color = Cell.Interior.Color And _
CellC.Font.Bold = Cell.Font.Bold And _
CellC.Font.Italic = Cell.Font.Italic And _
CellC.Font.ColorIndex = Cell.Font.ColorIndex Then
'Add 1 to the number in variable i
i = i + 1
End If
'Continue with next cell
Next CellC
'Return number stored in variable i to UDF.
CountC = i
End Function
Where to put the VBA code?
- Copy VBA code.
- Press Alt + F11 to open the VB Editor.
- Press with left mouse button on "Insert" on the top menu, a pop-up menu appears. See image above.
- Press with left mouse button on "Module" to insert a module to your workbook. The module appears in the "Project Explorer", in this case, named Module1.
- Paste code to code window.
Count unique distinct cells by cell font properties (font color, cell color, bold or italic)
The image above shows a user defined function in cell D3 that counts unique distinct values in cell range A1:A20 based on the cell formatting in cell C3. This UDF also checks font color, cell color and bold/italic.
The formula in cell D3 returns 3 because the values in cell A5, A10, A16 and A17 match the cell formatting in cell C3. The values are 24, 5, 24 and 17. However, the unique distinct values are 24, 5 and 17. 3 numbers and 3 is returned to cell D3.
Custom function in cell D3:
The user defined function matches the font properties in cell C3 with each cell in cell range A1:A20, then counts unique distinct values in those matching cells.
User defined function
Function CountUDC(rng As Range, Cell As Range)
Dim CellC As Range, ucoll As New Collection
For Each CellC In rng
If CellC.Interior.Color = Cell.Interior.Color And _
CellC.Font.Bold = Cell.Font.Bold And _
CellC.Font.Italic = Cell.Font.Italic And _
CellC.Font.ColorIndex = Cell.Font.ColorIndex Then
On Error Resume Next
If Len(CellC) > 0 Then ucoll.Add CellC, CStr(CellC)
On Error GoTo 0
End If
Next CellC
CountUDC = ucoll.Count
End Function
Count values category
This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Excel categories
5 Responses to “Count cells based on background color”
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.
Brilliant, thanks Oscar! Now for another challenge - is this possible without a custom function? I'm not sure for custom functions but I know you can not Share a file for multiple users to access at any one time as an .xlsm with a macro, only as xlsx (we use Excel 2010). We use shared files on a shared network drive a lot and I've taken advantage of many of your array formulas to provide 'running' counts of filtered data that I would only be able to do with a macro otherwise for when people use the shared files but they are getting very complicated and a bit slow due to the number of rows of data. This functionality of counting format conditions would work very well with the many conditional formats we have set up in the files. Once again thank you for these custom functions, they're going to be a great help on files we don't need to have as shared, but act as reporting metrics only. Absolutely awesome Oscar, thank you!
Dave,
Now for another challenge - is this possible without a custom function?
No, I don´t think so. You can check if a value is for example bolded but working with multiple cells (arrays) doesn´t seem to work.
https://www.ozgrid.com/forum/showthread.php?t=58202
https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html
This functionality of counting format conditions would work very well with the many conditional formats we have set up in the files.
Unfortunately, the macros don´t work with conditional formatting, only manual formatting.
Thanks for looking Oscar. Interesting to see similar discussions too.
Hi Oscar,
Is there any possibilities that excel could provide a chart (bar) which counts based on color?
Thank you
regards,
Chris
Awesome, but now i have problem to use your formula the the color form conditional formatting doesn't make effect, would you solved this. Thanks