Count and sum conditional formatting in Google Sheets

When a rule supplies the color, count the rule’s underlying values. You do not need a color add-on: COUNTIF handles one condition, COUNTIFS handles several, and SUMIF or SUMIFS totals the matching amounts. These formulas follow value changes without reading cell colors.

Select a colored cell and open Format > Conditional formatting. Check both the condition and Apply to range, then use that same data range in your calculation. Google’s conditional-formatting guide also explains custom formulas and fixed column references.

One table for the examples

Put these headings and records in A1:C8. Enter the C-column formulas as formulas, not text; leave the indicated cell empty. They make real dates relative to today so the examples remain reproducible. For your own records, use their actual due dates and numeric amounts.

Keep the summary formulas outside this table, such as in F2 onward. The ranges below exclude the heading and have matching row boundaries.

Count a status and sum its amounts

Suppose A2:A8 turns yellow when Text is exactly Unpaid:

=COUNTIF(A2:A8, "Unpaid")
=SUMIF(A2:A8, "Unpaid", B2:B8)

The results are 5 unpaid records and 520 in unpaid amounts. SUMIF checks A but adds B; it does not add the colored cells’ text. See Google’s COUNTIF and SUMIF references.

Count amounts above a threshold

If B2:B8 is colored when Greater than 100, use the same strict comparison:

=COUNTIF(B2:B8, ">100")
=SUMIF(B2:B8, ">100")

The results are 3 matching amounts, totaling 480. A rule using Greater than or equal to needs ">=100" instead. A value exactly on the boundary must qualify in both places or neither; matching the paint’s name is not enough.

Count overdue and unpaid records

For a rule that highlights whole rows only when they are unpaid and have a past due date, apply this Custom formula is rule to A2:C8:

=AND($A2="Unpaid", ISNUMBER($C2), $C2>0, $C2<TODAY())

The dollar signs fix the tested columns while the row number advances. These calculations use the same conditions:

=COUNTIFS(A2:A8, "Unpaid", C2:C8, ">0", C2:C8, "<"&TODAY())
=SUMIFS(B2:B8, A2:A8, "Unpaid", C2:C8, ">0", C2:C8, "<"&TODAY())

The results are 2 overdue unpaid records, totaling 320. Paid rows, future dates, today’s date and the blank due-date cell are excluded. ">0" avoids treating a blank date as an old date. Use real date cells, not text such as yesterday. SUMIFS puts the amounts range first, unlike SUMIF; Google’s COUNTIFS and SUMIFS pages describe the argument order.

What these totals mean

These are matching records, not a count of every visibly colored cell. Highlighting three cells across one row still counts as one record here. If several rules use the same color, counting one condition does not count all appearances of that color. Overlapping rules and their priority can also change the displayed appearance; do not simply add overlapping counts.

Manually painting a cell does not change whether these formulas include it. If your colors are manual labels rather than conditional rules, see counting manually colored cells. ColorSum supports manually applied fill and text colors; it does not promise to read conditional-formatting colors. For these rule-based examples, the native formulas above are the free solution.