SUMIFS Generator
Add up values only on the rows that satisfy every condition you set.
SUMIFS
Add up values only on the rows that satisfy every condition you set.
The numbers to add. In SUMIFS this argument comes first, unlike SUMIF.
Column checked against the first condition. Must be the same size as the sum range.
Condition for the first range. Text and comparisons need quotes; cell references do not.
Column checked against the second condition. Repeat the same range to build a between test.
Condition for the second range, for example ">100" or "<>Cancelled".
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100")Worked Example
A sales log with region in column A, units in column B, and order value in column C, and you want West orders over 100 units.
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100")Returns: Adds the column C values only for rows where the region is West AND units are greater than 100. Rows that meet just one of the two conditions contribute nothing.
Checks Before You Paste
- •The sum range comes FIRST in SUMIFS, the exact opposite of SUMIF where it is the optional last argument. Copying a working SUMIF and bolting an extra range onto the end is the most common way to get a silently wrong total.
- •Every criteria range must have the same height and width as the sum range. Pairing C2:C100 with A2:A101 returns #VALUE! in Excel and a mismatched-range error in Google Sheets.
- •To compare against a cell instead of a typed value, join the operator to the reference with &: ">"&F1. Writing ">F1" compares each value against the literal text F1 rather than the number in F1, which normally returns 0 with no warning.