Quick, clean, and to the point

Sum if cells are equal to

Excel formula: Sum if cells are equal to

To sum numbers based on cells being equal to a certain value, you can use either the SUMIF or SUMIFS functions. In the example shown, we are summing all sales in the West region. The formula in cell H6 is:


The formula in cell H7 is:


Both formulas refer to the named ranges "region" (C5:C30) and "amount" (E5:E30).


Both formulas use built-in functions to calculate a subtotal, but the syntax used by SUMIF and SUMIFS is slightly different:


In both cases, note that the region "West" must be enclosed in double quotes, since it is a text value.

Whether you use SUMIF or SUMIFS (which can handle more than one criteria) is a matter of personal preference. SUMIFS was introduced with Excel 2007, so it's been around now for a long time.

Dave Bruns

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.

Download 100+ Important Excel Functions

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