COUNTIF and SUMIF in Excel

COUNTIF counts the cells that meet a condition, and SUMIF adds the cells that meet a condition. For example, =COUNTIF(C2:C50,"Delhi") counts how many rows have "Delhi", and =SUMIF(C2:C50,"Delhi",D2:D50) adds up the sales figures for Delhi. These two functions turn a long list into an instant answer, which is why they show up in almost every office report.

When you need them

Plain SUM and COUNT work on a whole range. But real questions are usually conditional: how many students passed? How much did the North zone sell? What's the total of unpaid invoices? That "how many/how much where…" is exactly what COUNTIF and SUMIF answer.

COUNTIF — count with a condition

=COUNTIF(range, criteria)

• range — the cells to check.
• criteria — the condition, like "Delhi", ">=33", or "Paid".

Examples:

=COUNTIF(B2:B50,">=33")     how many marks are 33 or above (passes)
=COUNTIF(C2:C50,"Delhi")    how many rows are Delhi
=COUNTIF(D2:D50,"Pending")  how many invoices are pending

Text criteria go in double quotes. So do comparison criteria like ">=33".

SUMIF — add with a condition

=SUMIF(range, criteria, sum_range)

• range — the cells to check the condition against.
• criteria — the condition.
• sum_range — the cells to actually add (often a different column).

Example: to total sales for Delhi, where cities are in C and sales in D:

=SUMIF(C2:C50,"Delhi",D2:D50)

Excel looks down column C, finds every "Delhi", and adds the matching values from column D.

Worked example (zone-wise sales)

• Number of North sales: =COUNTIF(B2:B5,"North") → 2
• Total North sales: =SUMIF(B2:B5,"North",C2:C5) → 70,000
• Total South sales: =SUMIF(B2:B5,"South",C2:C5) → 80,000

Two or more conditions: COUNTIFS and SUMIFS

When there's more than one condition, add the "S":

=COUNTIFS(B2:B50,"North",C2:C50,">30000")
=SUMIFS(D2:D50,B2:B50,"North",C2:C50,">30000")

Note the order flips in SUMIFS: the sum_range comes first, then each range–criteria pair.

Using cell references as criteria

Instead of typing "Delhi", point to a cell. If F1 holds the zone name:

=SUMIF(B2:B50,F1,C2:C50)

Now you can change F1 and the total updates. This is how simple dashboards are built.

Pro tips

• Use >, <, >=, <=, <> (not equal) inside the quotes: ">=1000", "<>Paid".
• Wildcards work: "Del*" matches Delhi, Delta, anything starting with "Del". "?at" matches cat, bat, hat.
• Point criteria to a cell so your report updates when you change one input.

Common mistakes

• Mismatched range sizes. In SUMIF, the range and sum_range should cover the same rows (C2:C50 and D2:D50), or results go wrong.
• Forgetting quotes on comparisons. It's ">=33", not >=33. The whole condition sits inside quotes.
• Wrong order in SUMIFS. SUMIF puts the sum_range last; SUMIFS puts it first. Mixing them up is the most common error.
• Extra spaces in data. "Delhi " with a trailing space won't match "Delhi". Clean the column if counts look low.

Key takeaways

• COUNTIF counts cells meeting one condition; SUMIF adds them.
• SUMIF syntax: range, criteria, sum_range.
• Use COUNTIFS/SUMIFS for two or more conditions (sum_range first in SUMIFS).
• Point criteria to a cell to build a mini dashboard.

Practice task

Make a list of 10 orders with columns Product, City and Amount. Write formulas to count how many orders came from Mumbai and to total their amount. Then use SUMIFS to total Mumbai orders above ₹500.

COUNTIF and SUMIF are where Excel starts feeling powerful. Learn the full toolkit in the ADCA program at HCI.