Gridwise
Sign inStart free
Conditional function

How to use SUMIFS in Excel

Adds values whose row matches every condition.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, …)
sum_rangeThe cells to add
criteria_range1First range to test
criteria1First condition

Example

Here’s SUMIFS on a small table of orders. The highlighted cells are the ones the formula reads.

G2
fx
=SUMIFS(F2:F9,B2:B9,"East",C2:C9,"Omar")
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
54Result of =SUMIFS(F2:F9,B2:B9,"East",C2:C9,"Omar")

Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.

Common mistakes with SUMIFS

The sum range comes first

SUMIFS(sum_range, criteria_range1, criteria1, …). This is the reverse of SUMIF, where the sum range is last.

Ranges must be the same size

Every range needs the same number of rows, or you get #VALUE!.

What the error messages mean

Type these slips into Gridwise and this is the feedback you get:

Leaving off the closing bracket

=SUMIFS(F2:F9,B2:B9,"East",C2:C9,"Omar"

Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).

Text without quotes

=SUMIFS(F2:F9,B2:B9,East,C2:C9,"Omar")

Text needs quotes. East isn’t a function or a cell, so Excel can’t read it. If you mean the word, write "East" with quotes.

Questions

What does the SUMIFS function do in Excel?

Adds values whose row matches every condition. The syntax is SUMIFS(sum_range, criteria_range1, criteria1, …).

Why is my SUMIFS formula giving the wrong answer?

The sum range comes first: SUMIFS(sum_range, criteria_range1, criteria1, …). This is the reverse of SUMIF, where the sum range is last. Ranges must be the same size: Every range needs the same number of rows, or you get #VALUE!.

Does SUMIFS work in Google Sheets?

Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.

Related functions