The SUMIFS function

Adds the cells that meet every one of several conditions. The sum range comes first.

How to use SUMIFS

SUMIFS adds amounts when several conditions are true at the same time. Think of a sales list where you only want the North region and only shoes: a row counts when it passes every test.

The big difference from SUMIF is the argument order. SUMIFS starts with the range to add, then takes pairs of a range to check and its condition: =SUMIFS(C2:C10,A2:A10,"North",B2:B10,"Shoes"). Because the sum range comes first, you can add as many pairs as you need, but every range must have the same number of rows.

The conditions are joined with AND. For a period, use the same date column twice, once with ">="&F1 and once with "<="&F2. Operators, wildcards such as "*shoe*", and cell references work as in SUMIF.

With a single condition, SUMIF is a little shorter. To count the matching rows instead of adding them, use COUNTIFS, which has no sum range at all. When either of two conditions may be true, add two SUMIFS together.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range
The cells to add.
criteria_range1
The cells the first condition is checked against.
criteria1
The first condition.
criteria_range2, criteria2 (optional)
More range and condition pairs, up to 127.

Examples

E1=SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Shoes")
ABCDE
1RegionProductSales920
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

In plain words: Add up the sales in C2:C6 on the rows where the region in A2:A6 is North and the product in B2:B6 is Shoes.

E1=SUMIFS(C2:C6,A2:A6,"South",C2:C6,">200")
ABCDE
1RegionProductSales300
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

Common SUMIFS mistakes

  • SUMIFS returns #VALUE!

    The ranges are not the same size, as in =SUMIFS(C2:C6,A2:A7,"North"). Make every range cover the same rows.

  • Changing SUMIF to SUMIFS gives 0, an error or the wrong total

    The sum range moved. SUMIF ends with it and SUMIFS starts with it, so =SUMIF(A2:A6,"North",C2:C6) becomes =SUMIFS(C2:C6,A2:A6,"North").

  • SUMIFS returns 0 when I ask for two values in the same column

    The conditions are joined with AND, and no cell is both North and South, so =SUMIFS(C2:C6,A2:A6,"North",A2:A6,"South") gives 0. Add two SUMIFS instead: =SUMIFS(C2:C6,A2:A6,"North")+SUMIFS(C2:C6,A2:A6,"South").

  • A date condition adds nothing

    A date typed inside quotes depends on the date settings of the file, and dates stored as text never match. Build the date with DATE: =SUMIFS(C2:C100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<"&DATE(2024,2,1)) adds up January. Also check that the dates are real dates and not text.

Practice SUMIFS

Read the guide

Questions

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first.

How do I sum between two dates?

Use the date column twice, with the start date in F1 and the end date in F2: =SUMIFS(C2:C100,A2:A100,">="&F1,A2:A100,"<="&F2).

Can SUMIFS use OR instead of AND?

No, the conditions are always joined with AND. When either condition may be true, add two SUMIFS: =SUMIFS(C2:C6,A2:A6,"North")+SUMIFS(C2:C6,A2:A6,"South"). SUMPRODUCT can also do it in one formula.

Is SUMIFS case sensitive?

No. Text conditions ignore upper and lower case, so "north" and "North" match the same cells.

Related functions