FREE PRACTICAL LESSON · OCTOBER 3, 2026

SUMIF versus COUNTIF: choose the right question

Start with the question, not the function name. Do you need a total amount, or a number of rows? SUMIF adds matching values. COUNTIF counts matching cells. The plural forms, SUMIFS and COUNTIFS, support multiple conditions.

Try it with four records

Put this fictional data in A1:C5.

Add matching amounts

=SUMIF(A2:A5,"East",C2:C5)

This adds 40 + 30 + 50 and returns 120. The first range contains the region labels; the final range contains the amounts to add.

Count matching records

=COUNTIF(B2:B5,"Paid")

This returns 3 because three records have Paid status. It does not add their amounts.

Apply two conditions

=SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Paid")
=COUNTIFS(A2:A5,"East",B2:B5,"Paid")

The first returns 90, and the second returns 2. Both conditions must be satisfied on the same row. Notice that SUMIFS puts the amount range first, whereas SUMIF places it last.

A short checking habit

Mark the rows that qualify and calculate the expected answer by hand for this small example. Keep corresponding ranges aligned and check that amounts are numbers rather than imported text. If you compare regional totals with the overall total, confirm that the categories cover every record without overlap.

Use an explicit status or reporting period when real data requires one. Paid, Open, dates, and currencies are business definitions: a formula cannot decide what the report should mean for you. Save a copy before experimenting with important work data.

Continue with guided practice

Add and count matching records, combine conditions, and reconcile your answers using a small fictional orders dataset. The downloadable pack costs $6.99 before any applicable tax.

See the course and included files

Sources and further help

Microsoft: SUMIF · Microsoft: SUMIFS · Microsoft: counting matching values

Examples here are original and fictional. Browse all free lessons.