Formula guide · Excel and Google Sheets · Updated May 13, 2026
Count Colored Cells in Excel or Google Sheets
Count cells by color using a safer helper-column approach instead of relying on formatting as data.
Quick Answer
Spreadsheets do not treat color as normal data, so the reliable approach is to count a helper status column with COUNTIFS.
Copyable Formula
=COUNTIFS(E2:E100,"Approved")Syntax
Best practice: add a helper status column, then use COUNTIFS.Excel and Google Sheets
Worked Example
| E | Meaning |
|---|---|
| Status | Count target |
| Approved | count |
| Pending | ignore |
| Approved | count |
Result: Counts rows where Status is Approved, even if the visual color changes later.
Steps
- Translate each color into a real status value.
- Use COUNTIFS to count the status value.
- Use conditional formatting to apply color from the status column if needed.
Common Mistakes
- Using manual colors as the only source of truth.
- Counting colors after conditional formatting rules change.
- Adding custom scripts when a status column would be easier to audit.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.