Count and Sum Colored Cells in Excel

To count colored cells in Excel, you need a workaround, because no built-in function reads a cell's color. COUNTIF, SUM and the rest look at values, not formatting. The best method depends on where the color came from: filter by color and use SUBTOTAL for colors you painted by hand, or count the rule itself when conditional formatting did the coloring. This guide shows four methods on one small table, from the quickest to the most reliable.

All the examples use this score list in A1:B8. Scores under 50 are filled red.

The right answer is 3 red cells, with a total of 131 (45 + 39 + 47).

Why can't COUNTIF count colored cells?#

COUNTIF checks what is in a cell, such as a number or text, not how the cell looks. A fill color is formatting, and Excel's worksheet functions ignore formatting. So =COUNTIF(B2:B8,"red") counts cells that contain the word red, and returns 0 here. The COUNTIF function guide lists what the criteria can match.

Method 1: Filter by color, then use SUBTOTAL#

This is the fastest no-code way to count and sum colored cells. A filter hides the rows you don't want, and SUBTOTAL ignores hidden rows.

1. Click any cell in the table and turn on the filter: Data > Filter (Windows: Ctrl+Shift+L; Mac: ⌘⇧F).
2. Click the arrow on the Score header, point to Filter by Color, and pick the red fill.
3. Click an empty cell in the header row, such as D1. The filter never hides row 1, so the result stays visible. Type:

=SUBTOTAL(103,B2:B8)

The result is 3. To sum the same cells, use function number 109:

=SUBTOTAL(109,B2:B8)

Put it in E1. The result is 131. Clear the filter and both formulas go back to counting and adding every row: 7 and 406.

Filter by Color works for colors you applied yourself and for colors from conditional formatting. The guide to the SUBTOTAL function and the SUBTOTAL reference page explain the other function numbers, and summing only visible cells shows the same idea with AGGREGATE. If filters are new to you, read how to filter data in Excel or practise filtering a table.

Method 2: Find all cells with a fill color#

Find and Replace can search by format and tells you how many cells it found. This works in Excel for Windows:

1. Select B2:B8 and press Ctrl+F.
2. Click Options, then Format > Choose Format From Cell, and click one red cell.
3. Click Find All. The bottom of the dialog shows 3 cell(s) found.
4. To get a sum too, press Ctrl+A while the results list is active. Excel selects those cells, and the status bar at the bottom of the window shows their Count and Sum.

This is a quick one-off check. It doesn't leave a formula behind, so the number won't update when colors change. It also only finds colors you applied directly, not colors from conditional formatting.

Method 3: A short VBA function#

A small VBA function can read the fill color of each cell. VBA is the macro language built into desktop Excel. Use it only if you are comfortable with macros:

1. Press Alt+F11 (Mac: Tools > Macro > Visual Basic Editor) to open the editor.
2. Choose Insert > Module and paste this code:

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

3. Close the editor. Fill cell G1 red as a sample, then type =CountColor(B2:B8,G1) in any empty cell. The result is 3.
4. Save the file as an Excel Macro-Enabled Workbook (.xlsm), or the code is lost.

Know the limits before you rely on it:

• Changing a color does not trigger a recalculation, so the result can be out of date. In Excel for Windows, press Ctrl+Alt+F9 to recalculate everything. On any version, retyping a value in the range also refreshes it.
• It reads only colors you applied by hand. Colors from conditional formatting are invisible to it.
• Macros don't run in Excel for the web, and many companies block them.

Method 4: Count the rule, not the color#

When a color means something, count that meaning instead. In this table, red means "score under 50". So:

=COUNTIF(B2:B8,"<50")

returns 3, and

=SUMIF(B2:B8,"<50")

returns 131. These formulas update instantly, work in every version of Excel and never miss a cell.

If people color cells by hand to mark something, such as "paid" or "urgent", add a Status column and type the word instead. Then COUNTIF does the work, a conditional formatting rule can add the color automatically, and you can still sort the list by color or by status. Color then becomes a display of your data, not the data itself.

Which method should you use?#

Pick the method that matches where the color came from:

Practise it#

Counting with a condition is the skill behind the most reliable method, and it is a common job-test task. Try the exercise on this page, then practise COUNTIF in a live spreadsheet, where each formula is checked instantly. To get fast with filters and SUBTOTAL too, practise filtering a table.