Count and sum by font color in Google Sheets™
If you mark a spreadsheet by changing the text color, count that color rather than the cell background. Here is a small example using manually applied, single-color text, with a free status-based alternative and ColorSum’s sidebar and formulas.
A small example
Enter this table in A1:B5. Apply red text (#ff0000) to A2:B2, A4:B4 and A5:B5; keep A3:B3 black.
Leave the cell backgrounds white. In D1, type Red sample and give it the same red text color.
| Row | A: Status | B: Amount | Text color in A and B |
|---|---|---|---|
| 2 | Paid | 12 | Red |
| 3 | Late | 35 | Black |
| 4 | Paid | 8 | Red |
| 5 | Paid | 5 | Red |
The expected red count is 3, and the red amounts total 25.
When the color represents a status: use the status for free
If red means “Paid,” the sheet already has the information needed to count and total paid rows:
=COUNTIF(A2:A5, "Paid")
=SUMIF(A2:A5, "Paid", B2:B5)
These return 3 and 25 in the example. Google’s COUNTIF documentation explains matching a text criterion; SUMIF applies the criterion to one range and adds another. Neither requires an add-on. They follow the status values, so changing only a font color will not change which rows qualify. This is also useful when a conditional formatting rule colors the text according to that status.
Count the actual text colors with ColorSum’s free sidebar
Install ColorSum, then:
- Select B2:B5, the amounts in the example.
- Open Extensions > ColorSum > Open ColorSum.
- Choose Text color, then Count the selected cells.
The red row shows 3 cells and a sum of 25. Selecting A2:A5 instead counts the red status cells; those are text values, so they have no numeric sum. Choose Fill color only when you mean the background rather than the letters.
The sidebar is free for 10 counts a day, including its sums and averages. It reports the selection when you click Count; count again after changing the data or colors.
Keep the result in a cell with ColorSum Pro
The live formulas require ColorSum Pro: $9 once per Google account. Formulas recheck the purchase in each spreadsheet at least every 30 days; open ColorSum or refresh its formulas if asked. Existing subscriptions and trials keep their original terms. After opening ColorSum in the spreadsheet with Pro, put these in empty cells outside A2:B5:
=COUNTBYFONTCOLOR(A2:A5, D1)
=SUMBYFONTCOLOR(B2:B5, D1)
They return 3 and 25. D1 supplies its text color; its background and the words Red sample
do not choose the color. You can replace D1 with the quoted code "#ff0000".
If the statuses are colored but the amounts are not, use the status range to choose the rows and the third argument for the numbers:
=SUMBYFONTCOLOR(A2:A5, D1, B2:B5)
The two ranges must have the same number of rows and columns. Keep the references unquoted;
quote a literal color code. To build this through the sidebar, count A2:A5 with Text color,
click the red color, choose Sum, and enter B2:B5 in Numbers range. Select an empty output
cell outside both ranges, then click Insert in the selected cell.
After recoloring, choose Refresh color formulas in the sidebar or ColorSum menu. Pro also offers Refresh them when a color changes in this spreadsheet. This matters because a change to color alone does not recalculate the formulas. For a new selection, count it again before building another formula.
For backgrounds instead, see counting colored cells or summing another column by color.