Excel COUNTIF Highlighted Cell? Use These Methods

Formulas & functions · Updated November 22, 2026

No, Excel’s COUNTIF function cannot directly count cells based on their fill color or font color, because colors are formatting, not data. However, you can count highlighted cells using one of three reliable methods: filter by color with SUBTOTAL, use the GET.CELL macro function with a named range, or write a simple VBA macro. The method you choose depends on whether you need a one-time count, a live updating count, or a permanent solution.

Why COUNTIF Can’t Count Colors

COUNTIF works by evaluating the values in a range against a condition, such as =COUNTIF(A1:A10,"Yes"). It has no awareness of formatting. When you manually highlight a cell yellow, Excel stores that as a display property, not as part of the cell’s data. The formula engine only sees the underlying value. This is by design—formatting is meant to be visual, not computational. As a result, there is no built-in function like COUNTIFCOLOR or COUNTIFBYCOLOR in any version of Excel.

Method 1: Filter by Color + SUBTOTAL (No Formulas)

This is the fastest way to get a count without any formulas or macros. It works in Excel 2010 and later, including Excel for Microsoft 365, and on both Windows and Mac.

  1. Select your data range (for example, A1:A20).
  2. Go to Data > Filter (or press Ctrl+Shift+L on Windows, Cmd+Shift+L on Mac).
  3. Click the dropdown arrow in the header cell.
  4. Choose Filter by Color and select the fill color you want to count.
  5. Look at the status bar at the bottom of Excel—it will show “Count: 5” for the visible (filtered) cells.

If you want a permanent formula that updates when you change the filter, use SUBTOTAL. For example, with your data in A1:A20 and a header in A1, enter this in any cell:

=SUBTOTAL(103,A2:A20)

This returns the count of visible cells in the range. The 103 function number counts non-empty visible cells. When you filter by color, the formula recalculates automatically. This method is simple, but it only works if you manually filter—it won’t count colors automatically without user action.

Method 2: GET.CELL with a Named Range (Live Count)

For a live-updating count that doesn’t require filtering, you can use the old XLM macro function GET.CELL through a named range. This works in Excel 2010 through Microsoft 365 on Windows. On Mac, it may not work in all versions—test carefully.

Here’s how to set it up:

  1. Select cell B1 (or any empty cell).
  2. Go to Formulas > Define Name.
  3. In the “Name” box, type CellColor.
  4. In the “Refers to” box, enter: =GET.CELL(38,Sheet1!A1) (replace Sheet1 with your sheet name).
  5. Click OK.

Now, in B1, enter =CellColor. This will return a number representing the fill color of A1. For example, yellow is usually 6, green is 10, red is 3, but the exact number depends on your color palette. To count all cells in A1:A20 that have the same color as A1, use:

=SUMPRODUCT(--(CellColorRange=CellColor))

But wait—this only works if you create a named range for each cell individually, which is impractical for large ranges. A better approach is to use an array formula with GET.CELL in a helper column. For instance, put this formula in B1 and drag down to B20:

=GET.CELL(38,A1)

This requires the named range trick to work in each row. Actually, a simpler way is to use the CELL function, but it doesn’t return color. So the practical approach is: use the named range CellColor with a relative reference. If you define the name as =GET.CELL(38,Sheet1!A1) and then in B1 enter =CellColor, it will return the color of A1. But if you copy that formula down, it will still return A1’s color because the reference is absolute. To make it relative, define the name as =GET.CELL(38,Sheet1!A1) but use a relative reference: select B1, define the name with =GET.CELL(38,Sheet1!A1) (without $ signs), then in B1 enter =CellColor. When you copy down, the reference shifts relative to the formula’s row. So in B2, it will reference A2, and so on. Then you can use =COUNTIF(B1:B20,6) to count yellow cells. This method gives a live count that updates when you change a fill color.

Method 3: VBA Macro (Permanent and Flexible)

If you’re comfortable with macros, a custom VBA function is the most robust solution. It works on Windows and Mac (though Mac may require enabling macros differently). Here’s a simple function that counts cells with a specific background color:

Function CountColor(rng As Range, colorCell As Range) As Long
Dim cell As Range
Dim targetColor As Long
targetColor = colorCell.Interior.Color
For Each cell In rng
If cell.Interior.Color = targetColor Then
CountColor = CountColor + 1
End If
Next cell
End Function

To use it, press Alt+F11 to open the VBA editor, go to Insert > Module, paste the code, and close the editor. Save your workbook as a macro-enabled file (.xlsm). Then use the function in a cell like this:

=CountColor(A1:A20, B1)

Where B1 contains a cell with the fill color you want to count. The function returns the count. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.

Comparing the Methods: Which One Should You Use?

  • Filter + SUBTOTAL: Best for a quick, one-off count without any setup. No formulas to maintain, but requires manual filtering each time.
  • GET.CELL: Good for a live count that updates automatically when you change colors, but requires a helper column and named ranges. It can be fragile if you insert/delete rows.
  • VBA: Most flexible and powerful. You can create custom functions to count by color, sum by color, or even count by font color. Requires enabling macros, which some users avoid for security reasons.

For most users, the filter method is the easiest. For a permanent solution, VBA is the way to go.

Common Problems and Misconceptions

One common mistake is trying to use COUNTIF with a color condition, like =COUNTIF(A1:A10, "yellow"). This will not work because Excel doesn’t interpret color names as conditions. Another issue is that GET.CELL returns a color index that may change if you modify the theme or palette. Also, the filter method only counts visible cells, so if you have hidden rows, they are excluded. A frequent misconception is that conditional formatting colors can be counted the same way—they can, but the color index from GET.CELL reflects the displayed color, so it works for both manual and conditional formatting.

Alternatives: Using Helper Columns or Tables

If you need to count highlighted cells regularly, consider adding a helper column where you manually mark a value (e.g., 1 for highlighted, 0 for not). Then you can use COUNTIF on that column. Or, use an Excel Table and apply a filter, then read the count from the status bar. For more advanced needs, Power Query can also count colors, but it requires loading data and is more complex. In Google Sheets, you can use a custom function with Google Apps Script, but that’s a different program.

Frequently Asked Questions

Can I use COUNTIF with a cell color directly?

No. COUNTIF only works with cell values, not formatting. You must use one of the methods described above.

Why does my GET.CELL formula return a number that doesn’t match the color I see?

The number returned by GET.CELL(38) is a color index that depends on the current palette. It may not be intuitive (e.g., yellow might be 6, but light yellow could be different). To find the correct number, enter the formula in a cell next to a colored cell and note the result.

Will these methods work with conditional formatting?

Yes. Both GET.CELL and VBA read the displayed color, so they will count cells colored by conditional formatting. The filter method also works because it filters by the displayed color.

Can I count cells with a specific font color instead of fill?

Yes. In VBA, change Interior.Color to Font.Color. For GET.CELL, use argument 24 instead of 38 for font color. For filtering, choose “Filter by Font Color” instead of “Filter by Color.”

Is there a way to do this without macros or helper columns?

If you only need a one-time count, the filter method is the simplest. For an automatic count without macros, you could use a third-party add-in, but that’s not built-in. The GET.CELL method requires a helper column, but it’s a standard Excel feature.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.