COUNTIF vs SUMIF: counting rows vs adding values
Same 'if' idea, two different questions. COUNTIF answers 'how many rows match?' — it tallies cells. SUMIF answers 'what's the total of the values in matching rows?' — it adds a second column. Mixing them up is why a report shows 12 when you wanted $4,300, or vice-versa.
The differences at a glance
| COUNTIF | SUMIF | |
|---|---|---|
| What it returns | A count of matching cells | The sum of values in matching rows |
| Columns involved | One (the range you test) | Two (the range you test + the range you add) |
| Argument order | range, criteria | range, criteria, [sum_range] — sum_range LAST (opposite of SUMIFS) |
| Same-column shortcut | N/A | Omit sum_range to sum the tested column itself: =SUMIF(A:A,">100") |
| Multi-condition sibling | COUNTIFS | SUMIFS (sum_range moves to FIRST) |
| Compatibility | Universal | Universal — documented as identical across all three; executed in every LibreOffice version we test |
Which should you use?
- COUNTIF — You want a headcount: how many orders are 'East', how many scores are ≥90, how many cells contain 'error'. Only the test column matters.
- SUMIF — You want a filtered total: total sales for 'East', total hours on Project X. You're adding up a value column based on a condition in another (or the same) column.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both run correctly in every LibreOffice version we test; Google Sheets matches in our executed run; desktop Excel behavior matches per Microsoft's documentation — we do not run desktop Excel. The one true trap isn't compatibility — it's argument order: SUMIF puts the optional sum_range LAST, while its multi-condition upgrade SUMIFS puts sum_range FIRST. COUNTIF has no such column, so switching to COUNTIFS is painless. When in doubt, jump straight to COUNTIFS / SUMIFS for consistent syntax. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 10 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| COUNTIF — how many 'East' rows | =COUNTIF(A2:A100,"East") |
| SUMIF — total sales for 'East' | =SUMIF(A2:A100,"East",C2:C100) |
| SUMIF — sum the column itself where >100 | =SUMIF(C2:C100,">100") |
Full per-version details on each function page: COUNTIF · SUMIF.
See also: COUNTIF vs COUNTIFS · SUMIF vs SUMIFS.