How to count cells by font color in Google Sheets

By Jake Marsh · Updated

To count cells by font color in Google Sheets, install Huecount and use =COUNTBYFONTCOLOR(range, color), for example =COUNTBYFONTCOLOR(B2:B300, F1) to count the cells in B2:B300 whose text is the same color as the text in F1. Sheets has no formula that reads text color. Huecount also has SUMBYFONTCOLOR and AVERAGEBYFONTCOLOR, and the sidebar can list every text color in a range with its count.

When text color is the signal

Some sheets mark things by text color instead of fill: red text for items to reorder, blue for estimates that still need a real figure, gray for cancelled lines. A fill-color count misses all of these, because the cells have no fill. The font color functions read the color of the text itself.

Steps

  1. Install Huecount from the Google Workspace Marketplace and open the spreadsheet.
  2. Type a sample of the text color in a spare cell, for example the word "red" in red text in F1.
  3. Enter the formula where you want the count:
You wantFormula
Count cells with red text=COUNTBYFONTCOLOR(B2:B300, F1)
Sum column D where column B has red text=SUMBYFONTCOLOR(B2:B300, F1, D2:D300)
Average the numbers shown in blue text=AVERAGEBYFONTCOLOR(C2:C300, F2)
Count red text, skipping empty cells=COUNTBYFONTCOLOR(B2:B300, F1, "nonblank")
  1. Turn on auto recalculation. Open Extensions > Huecount > Open Huecount and tick Recalculate automatically when colors change, so changing a cell's text color updates the count a few seconds later.

See every text color at once

In the sidebar, enter the range, click Text color next to Fill color, then Count colors. You get one line for each text color in the range with the number of cells and, for numbers, the sum and average. Click a line and Insert formula to keep that number in the sheet.

Typing a color instead of using a sample

The color argument takes a hex code ("#cc0000"), the swatch name Sheets shows when you hover over it in the text color menu ("dark red 1"), several colors ("red|blue") or everything except one. Use "not" before a color to count every cell whose text is any other color, such as "not #cc0000".

Rules that change text color

Conditional formatting rules can change text color as well as fill. Huecount counts the text color you see, rules included. Add "nocf" to count only the text color set by hand.

What it costs

You can try Huecount for 14 days or 50 color breakdowns in the sidebar, whichever comes first, with no card, and the formulas work for the whole trial. Then it's $2.99 a month, $19 a year or $49 once for lifetime access per Google account. A $99 yearly team plan covers everyone at your company or school domain. See the Huecount page to install it.

Huecount: Count & Sum by Color

Count and sum cells by color in Google Sheets, with formulas that update when colors change $2.99 a month or $19 a year per Google account.


Questions

Can COUNTIF count by font color?

No. COUNTIF only compares values. COUNTBYFONTCOLOR takes the place of COUNTIF when the condition is the color of the text.

What's the difference between COUNTBYCOLOR and COUNTBYFONTCOLOR?

COUNTBYCOLOR matches the cell's fill (background) color, and COUNTBYFONTCOLOR matches the text color. Adding the "font" option to COUNTBYCOLOR gives the same result as COUNTBYFONTCOLOR.

Can I get a cell's text color as a value?

Yes. =CELLFONTCOLOR(A2) returns the text color as a code like #cc0000, which you can use inside COUNTIF, FILTER or QUERY.

Do other editors see the counts?

They need Huecount installed for the functions to calculate for them.


More Huecount guides


More Google guides