How to Count and Sum Cells by Color in Excel

The "how many yellow cells" problem

You color-coded an aging report. Yellow for follow-up. Green for paid. Red for dispute. Now your CFO wants the total dollar amount of yellow cells. Excel has no built-in =COUNTIF(range, "yellow") or =SUMIF(range, "yellow", amounts). The official Microsoft answer is "use Conditional Formatting to drive the colors and then COUNTIFS against the underlying condition," which doesn't help when the colors are manually applied.

Here are the three real ways to count and sum by color. The right one depends on whether the colors are manual or rule-driven and whether you need a recurring count or a one-shot answer.

TL;DR — Key takeaways

• The legacy GET.CELL(38, ...) trick uses Name Manager and an XLM4 macro function to expose color in formulas. It works but doesn't auto-recalc when colors change.
• A custom UDF in VBA (COUNTBYCOLOR, SUMBYCOLOR) is more elegant but has the same recalc problem.
• A VBA macro that runs once on demand gives you a definitive count and sum in a popup, with no stale-recalc problem.
• For colors that come from Conditional Formatting, none of the color-reading methods work; use COUNTIFS against the underlying condition instead.

Why Excel doesn't natively count by color

Cell color is a display property, not a value. Excel formulas operate on values, not on display. The reason there's no built-in COUNTIF for color: when you change a cell's color, Excel doesn't recalculate dependent formulas. There's no "color changed" event in the calc chain.

Every workaround has to deal with this recalc gap. The macros that read color do so on-demand, not reactively.

Method 1: GET.CELL(38, ...) via Name Manager

This is the Excel-old-school approach. GET.CELL is an XLM4 macro function (the predecessor to VBA). It's hidden, but you can use it inside a Defined Name.

1. Formulas > Name Manager > New.
2. Name: CellColor. Refers to: =GET.CELL(38, INDIRECT("RC", FALSE)).
3. Click OK.
4. In a helper column next to your data, type =CellColor.
5. Fill down. Each cell shows the color index of the cell to its left.
6. Use =COUNTIF(helper, color_index) and =SUMIF(helper, color_index, amounts) to count and sum.

The catch: the helper column doesn't auto-refresh when colors change. You have to press F9 to force recalculation. If you forget, the count is stale. Also, this only works in .xlsm files because XLM4 functions require macro permissions.

Method 2: Custom UDF in VBA

Write a User Defined Function:

Public Function COUNTBYCOLOR(rangeToCheck As Range, sampleCell As Range) As Long
    Dim cell As Range
    Dim color As Long
    color = sampleCell.Interior.Color
    For Each cell In rangeToCheck
        If cell.Interior.Color = color Then COUNTBYCOLOR = COUNTBYCOLOR + 1
    Next cell
End Function

Public Function SUMBYCOLOR(rangeToCheck As Range, sampleCell As Range) As Double
    Dim cell As Range
    Dim color As Long
    color = sampleCell.Interior.Color
    For Each cell In rangeToCheck
        If cell.Interior.Color = color And IsNumeric(cell.Value) Then _
            SUMBYCOLOR = SUMBYCOLOR + cell.Value
    Next cell
End Function

Drop these in a module, then use =COUNTBYCOLOR(B2:B100, D2) where D2 is a cell with the color you want to match.

The catch: same recalc problem. UDFs don't auto-trigger on color change. You have to F9 manually.

Method 3: The free VBA macro (one-shot, definitive)

Download Count and Sum Cells by Color. Free .xlsm with one macro.

Steps:

1. Select the range to scan.
2. Press Alt + F8, pick CountCellsByColor, click Run.
3. When prompted, click a single cell whose background color is the color to count.
4. The macro reports the matched color (hex), how many cells matched, how many of those were numeric, and the sum of the numeric matches.

This is a one-shot answer, not a formula. There's no recalc dance because there's no formula to recalc. Run again whenever you need a fresh count.

A common scenario: weekly aging report

Aging report with 4,200 invoices. Color-coded by team: yellow = follow-up scheduled, green = paid, red = dispute, no color = current.

With the macro:

1. Select column G (amounts).
2. Run the macro. Click a yellow cell. Get back: count and sum of yellow.
3. Repeat for green and red.
4. Drop the three numbers into the Tuesday status email.

About 30 seconds total. With formulas, you'd be maintaining a helper column with stale data half the time.

What about Conditional Formatting colors?

The macro reads cell.Interior.Color, which is the static fill color you applied manually. Conditional formatting colors don't show up there because they're computed at display time and don't get written to the cell's interior color property.

For Conditional Formatting-driven colors, count by the underlying rule instead:

• If the rule is "highlight if value > 1000", use =COUNTIF(B:B, ">1000").
• If the rule is "highlight if status = 'Open'", use =COUNTIF(C:C, "Open").

The rule is the source of truth; the color is a visualization. Counting by rule is more reliable than counting by color.

What if cells have different shades of "yellow"?

The macro matches by exact color value, not perceptual similarity. Two yellows that look identical to your eye but came from different theme picks (RGB 255,255,0 vs 255,255,153) count as different colors. Pick your sample cell from the same group you want to count.

Frequently asked questions

Does the macro count cells colored by Conditional Formatting?

No. The macro reads static Interior.Color. CF-driven colors aren't stored there. Use COUNTIF against the rule's underlying condition instead.

What about font color or border color?

The macro reads background fill only. To count by font color, change cell.Interior.Color to cell.Font.Color in the .bas source. Border color is more complex because cells have multiple borders (top, bottom, left, right) and border color isn't always uniform.

Will it sum cells that contain text or formulas?

Numeric formula cells count toward the sum. Text cells and error cells don't, but they still count toward the "matching color" total. The popup shows both counts so you can spot when text cells are inflating the count.

Can I get separate counts for multiple colors at once?

No, run the macro once per color. Each run takes about a second. The hex code in the popup makes it easy to remember which run was which color.

Can I undo it?

There's nothing to undo. The macro only reads cells; it doesn't modify them. The popup is the entire output.

What to do next

If your colored cells were marked manually for review (yellow = needs attention), pair this with Highlight Duplicate Values to make duplicates stand out. If you also want to find blanks in the same range, Highlight Blank Cells does it with the same revert-on-Ctrl-Z workflow.