SUMIFS
Add up the numbers in a range that meet one or more conditions.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required | Description |
|---|---|---|
sum_range | Required | The cells to add up. |
criteria_range1 | Required | The range tested against the first condition. |
criteria1 | Required | The condition to apply, such as "x", 100, or ">=100". |
criteria_range2 | Optional | A further range to test. Ranges and criteria are added in pairs. |
criteria2 | Optional | The condition applied to criteria_range2. |
Examples
Every example below was executed against VisiGrid engine 0.19.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=SUMIFS(A2:A4,B2:B4,"x") | 40 | Column A holds 10, 20, 30; column B holds x, y, x. Rows 1 and 3 match. |
=SUM(A2:A4) | 60 | For contrast — SUM takes every row, with no condition. |
When to use it
SUMIFS is the function to reach for whenever a total needs a condition
attached — revenue for one region, hours booked to one client, expenses in one
category. It is the workhorse of most real spreadsheets, and the source of most
of their silent errors.
The argument order trap
SUMIFS puts the range being added first. SUMIF, its older single-condition
sibling, puts it last:
=SUMIF(criteria_range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range, criteria)This is the single most common spreadsheet mistake we see, and it usually fails quietly: swapping the arguments often produces a number rather than an error, so nothing looks wrong. VisiGrid’s cell inspector shows which cells actually fed a result, which is the fastest way to catch it.
Every criteria range must be the same shape
Each criteria_range has to be the same height as sum_range. Mismatched ranges
are one of the classic ways a total drifts after someone inserts a row — the sum
range grows, the criteria range doesn’t, and the answer quietly changes.
Excel compatibility
Matches Excel's SUMIFS, including the sum_range-first argument order that differs from SUMIF. Criteria strings support the same comparison operators.