How to Count Colored Cells in Excel

To count cells with a particular fill color in Excel, filter the column by that color, then use =SUBTOTAL(103,A2:A101) to count its visible nonblank cells. COUNTIF does not read background colors. If a conditional formatting rule creates the color, you can instead count the values that meet that rule.

The numbered instructions below use desktop Excel for Windows, including Microsoft 365 and Excel 2024. Excel for Mac also supports color filtering, but its filter menu labels differ. If your web interface does not offer the needed color filter, open the workbook in desktop Excel. No macro is required for the filter method.

Choose What You Want to Count

Decide whether your total means filled-in cells, colored rows including empty cells, or records meeting a condition. Those are different questions. For example, five yellow cells can produce a nonblank count of four if one is empty. The helper-column method below handles that fifth row.

These examples count one column. If colors appear across several columns, work through each column separately and record its result. A row with three yellow cells contains three colored cells but only one row; choose the unit before adding totals.

Count Nonblank Colored Cells With Filter and SUBTOTAL

Suppose A1 is a heading and A2:A101 contains your data. Substitute your actual first and last data cells. Save the workbook before making changes, and clear unrelated filters if you want a total for the entire list. If you still need to apply fills, see how to color cells in Excel.

1. Select A1:A101, including the heading. For a list with related columns, select the complete data block so those rows stay together.
2. Go to Data > Filter. A dropdown arrow appears beside the heading. If your data is already an Excel table, its headings already have filter controls.
3. Open the arrow in column A, choose Filter by Color, then select the desired cell fill color. Rows with other fills are hidden. Choose cell color rather than font color.
4. In an unused cell on row 1 outside the filtered block, such as D1, enter =SUBTOTAL(103,A2:A101) and press Enter. Keep the total outside the counted range and above the filtered data rows.
5. Read the result, then inspect a few visible rows to confirm that you selected the intended color. For a small sample, compare the result with a manual count.

Microsoft explains the controls in Filter data in a range or table. Filtering hides rows; it does not delete their contents. Open the column dropdown and clear its filter when you want to show the other colors again.

In Microsoft’s SUBTOTAL documentation, 103 selects COUNTA while excluding filtered-out and manually hidden rows. The formula counts visible entries rather than inspecting their formatting. The color filter performs the color selection.

Include Colored Cells That Are Empty

Use a helper column when every matching row should count, even if its colored cell contains no value. Make sure the helper column is part of the same filtered range. Do not overwrite an existing data column; add an unused one.

1. For the example above, label B1 as Count Me and put the number 1 in every cell from B2 through B101.
2. Apply the filter to A1:B101, or the complete wider list if you have other columns. Filter column A by the desired fill color.
3. In an unused row-1 cell outside that block, enter =SUBTOTAL(109,B2:B101). The 109 option sums visible helper values; each 1 represents one row.

This is an application of the documented SUM option, not a color-reading function. Check that every intended row has exactly one helper value. Missing 1s undercount; a 2 counts that row twice. Keep a label beside your total so another reader knows whether it counts entries or rows.

Count the Condition Behind Conditional Formatting

For a simple rule that colors values greater than 100, an alternative is =COUNTIF(A2:A101,">100"). It counts the matching values even without applying a color filter. Keep the comparison and range aligned with the actual formatting rule; greater than 100 and greater than or equal to 100 produce different totals.

Microsoft’s COUNTIF instructions explicitly distinguish criteria from background and font colors. A value-based total can differ from the displayed-color total when several rules or manual fills are involved. Use color filtering if the question concerns the current appearance. Use a status column, such as Approved or Pending, if the color is merely a visual label for an underlying business category.

Why the Count Looks Wrong

Empty or apparently empty cells: COUNTA ignores genuinely empty cells but counts formulas returning empty text. Microsoft documents that distinction in its COUNTA reference. Use the helper-column method for a consistent row count.

Missing rows: Other active column filters can narrow the result further. Manually hidden rows are also excluded by 103 and 109; show them first if they belong in your total. Check that your formula stops at the actual last data row and excludes the heading.

Wrong color choice: Confirm that you filtered the column containing the fill, selected cell color rather than text color, and chose the correct swatch. Similar-looking shades can represent different categories. Do not recolor the source data just to force a desired count.

Frequently Asked Questions

Can COUNTIF count yellow cells directly?

No. Passing “yellow” to COUNTIF looks for a matching value; it does not inspect the fill. A VBA user-defined function is an advanced alternative documented by Microsoft, but it is unnecessary for the filter method above.

Can I count several colors?

Filter one color at a time and record each result with its color label. For a recurring report, keep the category in a separate data column so you can count it without relying on appearance.

How do I finish without leaving the sheet filtered?

Clear the color filter to show the full list again. Record your color-specific result first, because the SUBTOTAL value follows visible rows. Leave a note explaining the range, chosen color, and whether empty cells were included.