Exceljet

Quick, clean, and to the point

Sum if multiple criteria

Excel formula: Sum if multiple criteria
Generic formula 
=SUMIFS(sum_rng, crit_rng1, crit1, crit_rng2, crit2)
Summary 

If you need to sum numbers based on multiple criteria, you can use the SUMIFS function.

In the example shown, the formula in G6 is:

=SUMIFS(D4:D11,B4:B11,"blue",C4:C11,">15")
Explanation 

The first range (D4:D11) are the cells to sum, called the "sum range".

Criteria are supplied in pairs...(range / criteria).

The first criteria pair is (B4:B11 / "blue"). This means cells in B4:B11 must equal "blue".

The second criteria pair is (C4:C11 / ">15"). Cells in C4:C11 must *also* be > 15.

These cells in the sum range will only be summed when all criteria pass.

Author 
Dave Bruns
Related formulas 
Excel formula: Sum if equal to either x or y
Sum if equal to either x or y
In the example shown, we want to sum amounts for both the West and North regions. In other words, we want the total for amounts where region is either West or North. In the example shown, the formula in H6 uses SUMIFS twice: = SUMIF ( region ,...
Excel formula: Sum if date is between
Sum if date is between
The SUMIFS function allows logical operators (i.e. "=",">",">=", etc.), and can handle multiple criteria. To target values between two dates, we need to use two criteria , each entered as a criteria/range pair like this: ">" & H5 ,...
Excel formula: Count if two criteria match
Count if two criteria match
In this example, the goal is to count orders where the color is "blue" and the quantity is greater than 15. All data is in the range B5:B15. There are two primary ways to solve this problem, one with the COUNTIFS function, the other with the...
Excel formula: Sum sales in last 30 days by ID
Sum sales in last 30 days by ID
The SUMIFS function can handle multiple criteria when calculating a sum. In this case, SUMIFS is configured with a sum range for all amounts: = SUMIFS ( $D$5:$D$104 The first criteria specifies a criteria range that contains all dates, with a...
Related functions 
Excel SUMIFS function
Excel SUMIFS Function

SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when corresponding cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.