The fastest way to count colored cells in Excel is to use the SUBTOTAL function with a filter, or to write a short VBA macro that counts by fill color. There is no built-in COUNTIF that recognizes cell color, so you must use one of these workarounds. Below are the three reliable methods, how to choose between them, and the exact steps for each.
Why Excel Has No Built-In Color Count
Excel’s COUNTIF, COUNTIFS, and SUMPRODUCT functions evaluate values, not formatting. A cell’s fill color is a formatting property, not data, so the standard counting functions ignore it. This is by design: Microsoft keeps formulas dependent on values so that results update predictably when data changes. Color is meant for visual emphasis, not calculation.
That leaves three practical approaches: filtering with SUBTOTAL, using the Find and Replace tool, or writing a VBA macro. Each has trade-offs in speed, automation, and whether the result updates automatically.
Method 1: Filter and SUBTOTAL (No Code)
This is the simplest method and works in Excel 2010 through Microsoft 365. It counts visible cells after filtering by color.
- Select your data range (including headers).
- Press Ctrl+Shift+L (Mac: Cmd+Shift+L) to turn on AutoFilter.
- Click the filter arrow in the column that contains the colored cells.
- Choose Filter by Color and pick the fill color you want to count.
- In a cell below the data (or beside it), enter:
=SUBTOTAL(3,A2:A20)– assuming your data is in A2:A20. The3tells SUBTOTAL to count non-empty visible cells.
In this example, if the filtered range shows 7 rows, the formula returns 7. The result updates automatically when you change the filter color. To count multiple colors, you must repeat the filter and copy the result, or use the VBA method below.
Note: SUBTOTAL with function number 3 ignores rows hidden by the filter. If you have manually hidden rows, use =SUBTOTAL(103,A2:A20) to ignore both hidden and filtered rows.
Method 2: Find and Replace (Quick One-Off)
If you just need a one-time count and don’t need a formula, use Find and Replace:
- Select the range you want to search.
- Press Ctrl+F (Mac: Cmd+F) to open Find and Replace.
- Click Options > Format > Choose Format From Cell.
- Click a cell with the target fill color.
- Click Find All. The bottom of the dialog lists every matching cell; the count appears in the status bar (e.g., “7 cell(s) found”).
This is fast and requires no formulas, but the result is not dynamic. If you change a cell’s color, you must repeat the process.
Method 3: VBA Macro (Automated and Reusable)
For a permanent, automatically updating count, a small VBA function is the best route. It works in Excel 2016 and later, including Microsoft 365.
- Press Alt+F11 (Mac: Option+F11) to open the VBA editor.
- Go to Insert > Module.
- Paste the following code:
Function CountColor(rng As Range, colorCell As Range) As Long
Dim cell As Range
Dim targetColor As Long
Dim count As Long
targetColor = colorCell.Interior.Color
count = 0
For Each cell In rng
If cell.Interior.Color = targetColor Then
count = count + 1
End If
Next cell
CountColor = count
End Function
- Close the editor and save the workbook as a macro-enabled file (.xlsm).
- In a cell, enter:
=CountColor(A2:A20, B1)– where A2:A20 is the range to count, and B1 is a cell filled with the color you want to count.
The function returns the count of cells in the range that exactly match the fill color of B1. It updates automatically when any fill color changes. Note: this counts only solid fills, not gradient or pattern fills.
Important: Before running any macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z. If you’re unfamiliar with VBA, test on a copy of your data.
Which Method Should You Choose?
- Filter + SUBTOTAL – Best for quick, one-off counts without saving a macro. It’s also the only method that works in Excel for the web (though filtering by color is not available in the web version).
- Find and Replace – Fastest for a single count, but not reusable.
- VBA – Best for ongoing counts, multiple colors, or when you need the result to update automatically. Requires saving as .xlsm and enabling macros.
If you need to sum values in colored cells (not just count), use the same VBA function but change count = count + 1 to sum = sum + cell.Value and return a Double instead of Long.
Common Problems and Limitations
Several issues trip up users:
- Colors from conditional formatting: The VBA method above counts only manually applied fill colors. If the color comes from conditional formatting, use
cell.DisplayFormat.Interior.Colorinstead, but note that this only works in a standard module, not in a UDF called from a cell (Excel doesn’t allow DisplayFormat in UDFs). For conditional formatting, use the filter method or a different approach. - Merged cells: SUBTOTAL counts merged cells as one, but VBA counts each cell in the range individually, even if merged. This can overcount.
- Different shades: Excel treats light red and dark red as different colors. The VBA method compares exact RGB values, so a slightly different shade won’t match.
- Performance: VBA loops over every cell in the range. For very large ranges (100,000+ cells), this can be slow. Use the filter method for huge datasets.
Comparison with Google Sheets
Google Sheets also lacks a native color-count function. However, you can use Google Apps Script to write a custom function similar to the VBA one. In Sheets, the filter method works too: filter by color (right-click a cell > Filter by color) and use =SUBTOTAL(3,A2:A20). The VBA code does not run in Sheets; you’d need to translate it to JavaScript.
Frequently Asked Questions
Can I use COUNTIF with a color? No. COUNTIF only works with cell values, not formatting. There is no built-in function that counts by fill color.
Why does my SUBTOTAL formula return 0? Make sure the range you are counting contains values (or at least non-empty cells). SUBTOTAL with 3 counts non-empty cells, so if the colored cells are blank, it returns 0. Use =SUBTOTAL(103,A2:A20) to count only visible cells, but still requires non-empty.
Does the VBA function work with conditional formatting? No, the simple version does not. To count colors applied by conditional formatting, you must use a different approach, such as filtering by color, because DisplayFormat is not available in user-defined functions called from a cell.
How do I count multiple colors at once? The filter method requires one color at a time. For multiple colors, use the VBA function with a different color cell for each color, or write a more advanced macro that loops through a list of colors. Alternatively, use a helper column with the color index and COUNTIF.
Can I count colored cells in Excel for the web? Excel for the web does not support filtering by color or VBA. Your only option is to use the desktop version, or use a workaround like a helper column that stores a color code (e.g., using a formula to detect fill color, which is also not available in the web version).
Related
- Excel Y or N in Cell: How to Add Yes/No Dropdowns
- Excel XPS Export Options: How to Save as XPS
- XLSX or XLS Viewer: Which File Format Should You Use?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Zoom Shortcuts: How to Zoom In and Out QuicklyIn Excel for Microsoft 365 on Windows, press Alt, W, Q to open the Zoom dialog, enter a…
- How to Make Two Columns in WordTo make two columns in Microsoft Word for Microsoft 365 on Windows, open the Layout tab, select Columns,…
- Dropdown List in PowerPoint: Options and Setup GuidePowerPoint does not offer a built-in dropdown list like Excel’s data-validation menu. In PowerPoint for Microsoft 365 on…
- Excel Conditional Formatting for Duplicates: How It WorksYes. In Excel for Microsoft 365 on Windows, select the cells to check, then choose Home > Conditional…
- Google Sheets IF Else: How to Write Conditional FormulasGoogle Sheets does not have a separate ELSE keyword; use the IF function to test a condition and…
- Excel Shortcut to Select to the Bottom of a ColumnIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+Down Arrow to extend a selection from the active cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.