How to use Excel's SUMIFS Function

Sum rows that meet specifc criteria

What does Excel's SUMIFS function do?

The SUMIFS function is one of Excel's maths and trig functions.

The SUMIFS function returns the total value for rows in a dataset that meet the criteria that have been defined.

SUMIFS is an extremely useful function for obtaining the values to use in your reports.

Here's a simple example

Let's take a look at a simple example of the SUMIFS function.

We would like to sum up the total quanity sold for three different sets of criteria:

What does that mean in plain English?

1. Sum up the quantity sold for any row that has “Chicken Pies” in the product range.
2. Sum up the quantity sold for any row that has “January” in the month range.
3. Sum up the quantity sold for any row that has “Chicken Pies” in the product range and has “January” in the month range.

How do I write a formula using the SUMIFS function?

=SUMIFS(sum range, criteria range 1, criteria 1, criteria range 2, criteria 2, ... )

• sum range – What to sum up.
• criteria range 1– The first range to look in.
• criteria 1 – What to look for in criteria range 1.

What to consider when using the SUMIFS function in your financial model

• You can specify up to 127 criteria pairs.
• The criteria range must contain the same number of rows as the sum range.
• Use absolute cell references if you plan to copy the formula to other cells.
• SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?).
• Consider using Pivot Tables if you are performing one-off analysis.
• There are also COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS functions.

Read more about the SUMIFS function on the Microsoft support page here.