Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count cells by color in Excel and Google Sheets
Excel & Google Sheets
=COUNTIF(B2:B6,"Red")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Item | Category |
| Apples | Red |
| Bananas | Yellow |
| Cherries | Red |
| Grapes | Purple |
| Lemons | Yellow |
=COUNTIF(B2:B6,"Red")→2
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Excel, open the VBA editor (Alt+F11), insert a Module, and paste a custom CountByColor function that uses .Interior.Color to match cells.
- 2In Google Sheets, open Extensions > Apps Script, paste a custom function using getBackground() to compare hex color values.
- 3Save and close the editor, then call your custom function from a cell: =CountByColor(B2:B6, D1) where D1 contains a sample cell with the target color.
- 4Refresh results manually or add a dummy volatile argument (e.g. NOW()) to trigger recalculation when colors change.
- 5Note: custom color-count functions do not recalculate automatically when only the fill color changes — trigger a recalc by pressing F9 or editing any cell.
Tips
- Color-based counting is not supported by built-in formulas; the COUNTIF example above counts the text label 'Red', not the cell fill color.
- In Excel, the VBA function =CountByColor(range, colorCell) is the standard community approach.
- In Google Sheets, Apps Script getBackground() returns a hex string like '#ff0000'.
- Avoid relying on color as the sole data signal — store the category as text too (e.g. 'Red') so standard formulas work.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Is there a built-in Excel formula to count by color?
No. As of Excel 2024 and Microsoft 365, there is no native worksheet function for this. You need VBA or Power Query.
Why doesn't my custom function update when I recolor a cell?
Custom functions only recalculate when their input arguments change. Changing a fill color is not a value change, so you must manually trigger recalculation (F9 in Excel, or edit any cell in Sheets).