Count and sum cells by color in Google Sheets
Huecount adds COUNTBYCOLOR, SUMBYCOLOR and friends to Google Sheets. They take normal references you can drag, count conditional formatting colors, and update when a color changes.
Email us to get Huecount See pricingWhat it does
=COUNTBYCOLOR(A2:A50, D1) counts cells with the same fill as D1. =SUMBYCOLOR(A2:A50, "light green 3", C2:C50) works like SUMIF for colors.
Ranges are normal references, not text in quotes, so Sheets adjusts them when you copy or drag the formula.
Sheets never recalculates when only a color changes. Huecount reruns your color formulas a few seconds after you change one.
Colors from conditional formatting rules count too: text and number rules, dates, color scales and custom formulas.
The sidebar lists every color in a range with its count, sum and average, and writes the formula for you.
COUNTBYFONTCOLOR, SUMBYFONTCOLOR and AVERAGEBYFONTCOLOR, plus CELLCOLOR for your own COUNTIF and FILTER formulas.
Functions
| Function | What it returns |
|---|---|
COUNTBYCOLOR(range, color, [options]) | Number of cells with the fill color |
SUMBYCOLOR(range, color, [sum_range], [options]) | Sum of numbers in colored cells, or in the matching cells of sum_range |
AVERAGEBYCOLOR, MINBYCOLOR, MAXBYCOLOR | Average, smallest, largest. Same arguments as SUMBYCOLOR |
COUNTBYFONTCOLOR, SUMBYFONTCOLOR, AVERAGEBYFONTCOLOR | The same, by text color |
CELLCOLOR(range, [options]), CELLFONTCOLOR | The color a cell shows, like #d9ead3, or one per cell for a range |
The color can be a cell that has it, a hex code ("#ff0000"), the name Sheets shows when you hover a swatch ("light green 3"), several colors ("red|yellow"), or everything but one ("not white"). Options: "font", "nocf" to ignore conditional formatting, "nonblank", "tolerance=10".
Get started
- Install Huecount, open a spreadsheet and choose Extensions > Huecount > Open Huecount. This switches the functions on for your account.
- Select a colored range and click Count colors, then click a color and Insert formula. Or type
=COUNTBYCOLOR(in any cell. - Tick Recalculate automatically when colors change so your totals stay right.
Pricing
About $1.58 a month. Every function and the sidebar, in any number of spreadsheets, for one Google account.
Same features, billed monthly.
Lifetime access for one Google account. No renewals.
Team plan: everyone at your company's Google Workspace domain.
14 days or 50 color breakdowns in the sidebar, whichever comes first. The formulas work all through the trial. No card needed.
No surprise charges. We never charge you unless you start a checkout yourself. The add-on shows your trial status and these prices from the first minute. When the trial ends, the formulas show "your free trial has ended" instead of a number; nothing is charged and your sheet isn't changed.
Renewal. Monthly, annual and team plans renew automatically at the same price until you cancel. Cancel any time from Plans and billing in the add-on, in one step. You keep access until the end of the period you paid for. The lifetime plan is a single payment and never renews.
Right to cancel (UK and EU). If you're a consumer in the UK or EU, you can cancel within 14 days of buying and get a full refund. Email hello@greatwork.company or cancel in Plans and billing and reply to the receipt.
Mistakes. Charged twice or by accident? Email us and we'll refund it.
Prices are in US dollars and exclude any sales tax or VAT that applies where you live. Payments are processed by Stripe; we never see your card number.
Why switch
| What people complain about | What Huecount does |
|---|---|
| Ranges must be typed in quotes, so formulas can't be dragged | Normal references. Drag, copy and fill like any formula. Quoted ranges still work if you're moving over. |
| Doesn't recalculate when colors change | Automatic recalculation after any color or formatting change, plus a Recalculate now button. |
| Conditional formatting colors aren't counted | Counts the color you see. If a rule can't be read, the formula names the rule instead of giving a wrong number. |
| Asks to see all your spreadsheets | Works only in the spreadsheet you open it in. |
| Hard to tell what you'll pay | Prices on this page and in the add-on. A one-time option for people who'd rather not subscribe. |
Help
My total didn't change after I recolored a cell.
Turn on Recalculate automatically when colors change in the sidebar, or use Extensions > Huecount > Refresh color formulas now. Sheets itself only reruns formulas when values change.
The formula says it can't evaluate a conditional formatting rule.
Huecount re-checks your rules to know which color each cell shows. Most rules work; a custom formula that uses a function like VLOOKUP doesn't yet. Add "skipcf" to ignore that rule, or "nocf" to count each cell's own fill. Email us the rule and we'll look at adding it.
A coworker sees #NAME?.
Custom functions from an add-on only calculate for people who have the add-on installed. They can install Huecount; if you're on a paid plan, the formulas in your files keep working for them.
What counts as a trial use?
One Count colors breakdown in the sidebar. Formulas, inserting formulas and recalculating don't use any.
Still stuck?
Email hello@greatwork.company. A person replies within one business day.
Privacy
- Google data it accesses: the spreadsheet you open it in: cell colors, values and conditional formatting rules, to calculate totals, and the one cell you choose when you insert a formula. It can't open your other files, your Drive or your Gmail.
- Where things are stored: your license status and settings live in Google's add-on storage for your account and this spreadsheet. No cell values are stored anywhere.
- What reaches Great Work: your Google account email, the product id and a count of trial uses, for billing. Spreadsheet contents never leave Google. See the full privacy policy.
- Sharing: we don't sell data, use it for ads, or use it to train AI models.
- Huecount's use and transfer of information received from Google APIs adheres to the Google API Services User Data Policy, including the Limited Use requirements.