Formula guide · Excel and Google Sheets · Updated May 13, 2026
Sum by Color in Excel or Google Sheets
Understand the practical ways to total colored cells in a spreadsheet.
Quick Answer
The most reliable way to sum by color is to turn the meaning of the color into a real category column and then use SUMIFS.
Copyable Formula
=SUMIFS(D2:D100,E2:E100,"Approved")Syntax
Best practice: add a helper category column, then use SUMIFS.Excel and Google Sheets
Worked Example
| D | E |
|---|---|
| Amount | Status |
| 120 | Approved |
| 80 | Pending |
| 200 | Approved |
Result: Sums amounts where the helper Status column says Approved.
Steps
- Decide what each color actually means.
- Add a helper column with that meaning as text.
- Use SUMIFS against the helper column.
- Keep color as visual formatting, not the only source of report logic.
Common Mistakes
- Building important reports from manual fill colors only.
- Expecting SUMIFS to read formatting directly.
- Using scripts or custom functions before checking whether a helper column is simpler.
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.