How to Count Colored Cells in Excel

Excel doesn't ship with a built-in COUNTCOLOR function, and that frustrates spreadsheet users every single day. You highlight overdue invoices in red, mark completed tasks green, flag risky leads yellow — and then realise there's no tidy formula like =COUNTIF(A1:A100, "red") that just works. The good news? There are five reliable ways to count cells by colour, and a couple of them take less than thirty seconds.

This guide walks through all five, ranked from easiest to most powerful. We'll cover the quick Filter+SUBTOTAL trick, the Find & Replace dialog method, the legacy GET.CELL macro, a clean custom VBA in Excel function, and finally Power Query for spreadsheets that need it done automatically. Along the way you'll learn the critical difference between manually-coloured cells and conditional formatting — because most beginners get burned by that distinction first.

Whether you're tallying highlighted line items in an audit, summing red flagged budget lines, or just trying to count green status indicators in a project tracker — by the end of this article you'll know exactly which technique fits the job.

Method 1: Filter by Color + SUBTOTAL (the 30-second way)

This is the fastest method for anyone who needs an answer right now and doesn't want macros, add-ins, or VBA. It uses Excel's built-in colour filter combined with the SUBTOTAL function, which has the rare ability to count only visible rows.

Here's how it works step by step. Click anywhere inside your data range. Go to the Data tab and hit Filter (or press Ctrl+Shift+L). Click the little dropdown arrow on your column header. Choose Filter by Color and pick the colour you want to count. Excel hides every row that doesn't match.

Now drop a SUBTOTAL formula into an empty cell. To count visible cells:

=SUBTOTAL(3, A2:A100)

The 3 refers to COUNTA, which tallies non-empty cells. If you want to sum the values in coloured cells instead, swap the function number for 9:

=SUBTOTAL(9, A2:A100)

That 9 tells SUBTOTAL to use SUM. Other useful function numbers — 1 for AVERAGE, 4 for MAX, 5 for MIN, 2 for COUNT (numbers only). Compare this with the standard count formula in Excel which counts everything regardless of visibility.

The pros — zero setup, no macros, refreshes the moment you change the filter. The catch? You have to filter manually each time. If your colours change daily this gets tedious fast. Also: SUBTOTAL doesn't care why a row is hidden. Manually hidden rows count as filtered too. Keep that in mind when auditing.

Method 2: Find & Replace with Format

Slightly less famous but genuinely brilliant: you can use the regular Find dialog to count cells with a specific colour format. No formula, no VBA, just a dialog box.

Press Ctrl+F to open Find. Click Options >> to expand the dialog. Click the Format button (or the dropdown arrow next to it and choose Choose Format From Cell). Your cursor turns into an eyedropper. Click any cell with the colour you want to count. Leave the Find what field empty. Click Find All.

Excel pops up a list at the bottom of the dialog showing every cell with that format — and the status bar shows a running count. Quick, dirty, no formulas left behind in your sheet.

It's perfect for one-off audits. If your boss walks over and asks "how many red cells are in this report?", you can answer in about ten seconds. The downside is obvious: there's nothing to automate or paste into a dashboard. The count lives only inside that dialog and disappears when you close it. Repeating the process for blue, green, and yellow takes four passes.

One quirk — Excel doesn't always pick up custom theme colours the first time. If Find All returns nothing, click Clear next to the Format button and re-pick the cell. That usually does it.

Method 3: GET.CELL — the Hidden Excel 4.0 Macro

Now we get into territory most Excel users don't know exists. There's an old macro function called GET.CELL baked into Excel since version 4.0, and although Microsoft hid it behind the Name Manager, it still works in every modern version including Excel 365.

The trick: GET.CELL(63, reference) returns the background fill colour index of a cell. Once you have a column of colour indexes next to your data, you can COUNTIF them like normal numbers.

Setup takes a minute. Go to Formulas → Define Name (or press Ctrl+F3 then click New). Type a name like ColorCode. In the Refers to box paste:

=GET.CELL(63, Sheet1!A1)

Click OK. Now in column B next to your data, type =ColorCode and drag down. Each cell shows a number — that's the colour index for the cell immediately to the left.

To count red cells (colour index varies but usually 3 for the classic red), use:

=COUNTIF(B2:B100, 3)

To find the right number, type =ColorCode into a cell next to a known red cell and read the result. Then plug that number into your COUNTIF. The downside — you must save the workbook as a macro-enabled file (.xlsm) because GET.CELL is technically a macro function. Also, the helper column doesn't refresh automatically when you change a cell's colour. Press F9 to force a recalc.

Method 4: Custom VBA Function — CountByColor

If you want a real =COUNTCOLOR() function that behaves like every other Excel formula, you have to write one yourself. It takes ten lines of VBA and works forever. Here's the function — open the VBA editor with Alt+F11, insert a Module from the Insert menu, and paste this in:

Function CountByColor(rng As Range, colorRef As Range) As Long
    Dim cell As Range
    Dim target As Long
    target = colorRef.Interior.Color
    For Each cell In rng
        If cell.Interior.Color = target Then
            CountByColor = CountByColor + 1
        End If
    Next cell
End Function

Save your workbook as .xlsm. Back on the worksheet, use the new function like any built-in formula:

=CountByColor(A1:A100, B1)

Where A1:A100 is the range to scan and B1 is any cell holding the colour you want to match. To count blue cells, put a blue cell anywhere on the sheet and reference it.

Need a SUM version instead? Tiny tweak:

Function SumByColor(rng As Range, colorRef As Range) As Double Dim cell As Range Dim target As Long target = colorRef.Interior.Color For Each cell In rng If cell.Interior.Color = target And IsNumeric(cell.Value) Then SumByColor = SumByColor + cell.Value End If Next cell End Function

This is the cleanest approach for any spreadsheet you'll use again.

The function recalculates when other formulas trigger a refresh, though — like GET.CELL — Excel won't auto-recalc when you merely change a cell's fill colour. Tap F9 after recolouring. Many users add Application.Volatile as the function's first line to force recalc on every change, but be warned: that slows large workbooks.

Method 5: Power Query for Automated Reports

Power Query is overkill for a quick count but unbeatable when you want the answer to update automatically every time the data refreshes. Common scenario: a dashboard that imports a CSV daily, where the source colours rows by status and you need a live count of each status.

The catch — Power Query doesn't read cell fill colours from an Excel range directly. Most workflows skip the colour entirely and replicate the colouring rule as a logic step inside the query. If overdue invoices are red because Days Past Due > 30, you don't count the colour, you count the rows that match the rule.

Load the table — Data → From Table/Range. In the Power Query editor, add a custom column. Click Add Column → Custom Column and define your logic:

if [Days Past Due] > 30 then "Overdue"
else if [Days Past Due] > 0 then "Due Soon"
else "Current"

Then use Transform → Group By on that new column with Count Rows as the aggregation. Close & Load. The result is a tiny summary table that refreshes whenever your data does. Combine with the excel pivot tables workflow for slicing further.

If you genuinely need colour-based aggregation (maybe the colours were applied manually with no underlying rule), you have two options. First — accept that Power Query alone can't read fill colours and build a helper column with a VBA UDF that records the colour, then load that into Power Query. Second — use the script lab or Office Scripts in Excel 365 to enumerate cell colours and write them to a column, then query from there.

Cell Color vs Font Color — Don't Get Tripped Up

Every method above counts background fill colour by default. If you need to count font colour (the colour of the text inside the cell rather than the cell's shading), the parameters change. In GET.CELL, swap the 63 argument for 24 — that returns the font colour index. In the VBA function, replace .Interior.Color with .Font.Color.

It's a tiny edit but a huge gotcha. People copy a VBA snippet from a forum, paste it in, get zero matches, and assume the function is broken. It isn't — they coloured the text red rather than the background. Always confirm which one you want before troubleshooting.

The Conditional Formatting Trap (Read This Before You Spend Two Hours Debugging)

Here's the single biggest pitfall when counting coloured cells, and the one that catches almost everyone the first time. None of the five methods above can count cells coloured by Conditional Formatting. Not in stock Excel 2019, 2021, or 365. Why? Because conditional formatting is a display effect — the colour is applied at render time by the CF engine, and Excel's macro layer reads only the cell's actual formatting, not the temporary visual override.

Run the VBA CountByColor function on a column shaded entirely by conditional formatting, and you'll get zero matches. Use Find & Replace — same result. The cells look red on screen, but .Interior.Color returns the underlying default (white or transparent) because the CF rule fires only during display.

The fix is dead simple once you know it: count the rule, not the colour. If a CF rule says "highlight red when value > 1000", then to count the red cells just count cells where value > 1000:

=COUNTIF(A1:A100, ">1000")

This is faster than any colour-counting trick and always accurate. The colour was always downstream of the rule — work with the rule directly. For multi-condition rules use COUNTIFS or include them as criteria in the countifs function in excel guide.

If you absolutely must count the visible colour (maybe you don't know the underlying CF rule because someone else built the sheet), build a helper column that mirrors the rule and count that. Or, in Excel 365, use Office Scripts with getConditionalFormats() and inspect each cell's evaluated state — but that's a heavy lift for a problem that's usually trivial to solve by going to the source rule.

Which Method Should You Pick?

If this is a one-off question — use Find & Replace or Filter+SUBTOTAL. Both take under a minute, no macros required, no .xlsm save, no fuss.

If you'll repeat the count in the same workbook, go with the VBA function. Ten lines of code, and from then on it behaves like a native formula. Save as .xlsm and you're done.

If the colours come from a Conditional Formatting rule — skip the colour-counting entirely and use COUNTIF or COUNTIFS against the underlying rule. Faster, more reliable, no macros.

If the data refreshes automatically from an external source and you want the count to live in a dashboard, Power Query plus a logic column is the right call. It's the most upfront work but the most maintenance-free over time.

And if you're stuck on an older Excel version or in a locked-down corporate environment where macros are blocked, GET.CELL is the elegant workaround — though it still requires the file to be saved as macro-enabled. Filter+SUBTOTAL remains the only fully macro-free path in that scenario.

Closing Thoughts

Excel keeps surprising us. A function as obvious as =COUNTCOLOR() doesn't exist, yet there are five separate ways to get the same answer — some elegant, some ugly, all useful in the right context. Knowing which one to reach for separates spreadsheet users from spreadsheet operators.

The two takeaways worth pinning to your monitor: first, manual fill colours and conditional formatting colours behave completely differently — Excel can read the first but not the second. Second, the rule that created the colour is almost always easier to count than the colour itself. Work upstream when you can.

One more thing worth noting before you go. Microsoft has hinted at native colour-aware functions in upcoming Excel 365 builds. Office Insider channels have shown experimental BYCOL and LAMBDA-based recipes that hook into cell metadata directly. Until those go GA the five methods above remain your toolbox, and frankly even when COUNTCOLOR ships, the conditional-formatting caveat will probably still apply.

If you spend a lot of time inside Excel and want to sharpen these formula instincts, working through targeted practice questions is the fastest way to build them up. Try the practice test below to test your knowledge of count, sum, filter, and conditional logic functions in real scenarios.