They share the same syntax structure but answer different questions. Here's exactly when to use each.
SUMIF adds up values that match a condition. COUNTIF counts how many cells match a condition. Same structure, completely different output.
=SUMIF(range, criteria, [sum_range])
=SUMIF(A2:A100, "North", B2:B100)
-- Add all values in B where A = "North"
-- Answer: total sales from the North region=COUNTIF(range, criteria)
=COUNTIF(A2:A100, "North")
-- Count how many cells in A contain "North"
-- Answer: number of transactions from the North region| Question | Function | Formula |
|---|---|---|
| Total sales from North? | SUMIF | =SUMIF(region, "North", sales) |
| How many North transactions? | COUNTIF | =COUNTIF(region, "North") |
| Average sale in North? | AVERAGEIF | =AVERAGEIF(region, "North", sales) |
Text match: "North"
Not equal: "<>North"
Greater than: ">1000"
Less than: "<500"
Contains: "*excel*" (wildcard)
Starts with: "excel*"
Cell reference: A1 (no quotes needed)
With operator: ">"&A1 (combine operator and cell ref)Both have "S" versions for multiple conditions: SUMIFS and COUNTIFS. These work identically but accept more than one criteria range and criteria pair. Use SUMIFS and COUNTIFS by default — they handle single conditions too.
Asking "how much?" → SUMIF. Asking "how many?" → COUNTIF. That's the only decision you need to make.
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →