Cellwise Try it free

Excel guide · 2 min read

SUMIF vs SUMIFS: what is the difference?

Quick answerSUMIF adds values that meet one condition: =SUMIF(range, criteria, sum_range). SUMIFS adds values that meet all of several conditions, and the sum range comes first: =SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...). With one condition both give the same total. The flipped argument order is the thing that trips people up.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1RepRegionProductUnitsRevenue
2AnaNorthLaptop45200
3BenSouthMonitor102450
4AnaNorthMonitor61470
5ChrisWestLaptop22600
6BenSouthLaptop33900
7DanaNorthDock121080
8ChrisWestMonitor81960
9AnaSouthLaptop11300

One condition: same result, different order

Total revenue for the North region. SUMIF reads like a sentence: look in the regions, find "North", add the revenue. The sum range is last.

SUMIFS puts the sum range first, then the pairs: =SUMIFS(E2:E9,B2:B9,"North"). It returns the same 7,750.

=SUMIF(B2:B9,"North",E2:E9)

Result: 7750

7,750: Ana's 5,200 and 1,470 plus Dana's 1,080.

Two or more conditions: SUMIFS only

North and Laptop at the same time. SUMIF cannot do this. SUMIFS adds a row only when every pair matches.

Because the sum range is always first in SUMIFS, adding a third condition is just two more arguments at the end. That is why many people use SUMIFS even for one condition: the formula never needs rearranging.

=SUMIFS(E2:E9,B2:B9,"North",C2:C9,"Laptop")

Result: 5200

5,200: only Ana's first row is both North and Laptop.

Conditions on the numbers themselves

SUMIF has a shortcut: leave out sum_range and it adds the cells it is testing. Here it totals every sale over 2,000. In SUMIFS you repeat the range: =SUMIFS(E2:E9,E2:E9,">2000").

You can also test one column and add another, for example revenue from rows with 5 or more units: =SUMIFS(E2:E9,D2:D9,">=5").

=SUMIF(E2:E9,">2000")

Result: 14150

14,150 from the four sales above 2,000 (5,200, 2,450, 2,600 and 3,900).

OR logic: add two SUMIFs

Both functions are AND-only between conditions. To total Monitors or Docks, add one SUMIF per item. =SUMIFS(E2:E9,C2:C9,"Monitor",C2:C9,"Dock") returns 0, because no row is both.

=SUMIF(C2:C9,"Monitor",E2:E9)+SUMIF(C2:C9,"Dock",E2:E9)

Result: 6960

6,960: 5,880 from Monitors plus 1,080 from Docks.

Which one to pick

Use SUMIFS by default. It handles one condition or many, its argument order is the same as AVERAGEIFS and the other ...IFS functions, and it refuses mismatched ranges with a #VALUE! error instead of quietly giving a wrong answer.

SUMIF is fine for a quick one-condition total, especially if you like the shortcut of leaving sum_range out. Just be careful with range sizes: if sum_range is shorter or longer than range, SUMIF silently resizes it to match, starting from its first cell. That hides typos like B2:B9 with E3:E10.

Common mistakes

Copying a SUMIF into SUMIFS without reordering

SUMIF(B2:B9,"North",E2:E9) becomes SUMIFS(E2:E9,B2:B9,"North"). The sum range moves from last to first.

Mismatched range sizes

SUMIFS returns #VALUE! when ranges differ in size, which at least tells you. SUMIF quietly shifts the sum range, so check that both start on the same row.

Totals of 0 because the numbers are text

SUMIF and SUMIFS skip numbers stored as text in the sum range. If a total looks low, check with =COUNT(E2:E9) and convert the column.

FAQ

Can SUMIFS be used with only one condition?

Yes. =SUMIFS(E2:E9,B2:B9,"North") is valid and gives the same result as SUMIF. Many teams standardize on SUMIFS for that reason.

Why is SUMIFS returning 0?

Usually one condition does not match anything: an extra space in the data, a typo, or two conditions on the same column that can never both be true. Test each condition alone with COUNTIF to find the one that fails.

Do SUMIF and SUMIFS work with dates?

Yes. Use operators joined to a DATE, for example =SUMIFS(E2:E9,A2:A9,">="&DATE(2026,11,1)). Dates must be real dates, not text that looks like a date.

Are SUMIF and SUMIFS case-sensitive?

No. "north", "North" and "NORTH" all match. For a case-sensitive sum, use SUMPRODUCT with EXACT.

Functions used

Related guides

Updated