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

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 ,...

Sum if date is between

The SUMIFS function allows logical operators (i.e. "=",">",">=", etc.), and multiple criteria. To match target values between two dates, we need to use two criteria . The SUMIFS function requires that each criteria be entered as a criteria/range...

Count if two criteria match

The COUNTIFS function takes multiple criteria in pairs — each pair contains one range and the associated criteria for that range. To generate a count, all conditions must match. To add more conditions, just add another range / criteria pair...

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

SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent 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.