How to Use The SUMIFS Function

Hello and welcome to this blog post on the Excel function SUMIFS. In this blog post, I’ll show you how the SUMIFS function works, explain its syntax and provide you with some examples of how it can be used.

How The SUMIFS Function Works

The SUMIFS function perhaps unsurprisingly provides similar functionality to the SUMIF function. The key difference between the SUMIF function and the SUMIFS function, besides the extra S, is that the SUMIFS function allows you to specify multiple criteria.

Syntax

The syntax for the SUMIFS function is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

Parameter Description
sum_rangethe sum_range parameter is the range of values that will be summed if the various criteria are met. This parameter is required.
criteria_range1the criteria_range1 parameter is the range of values that is evaluated against the criteria1 parameter. This parameter is required.
criteria1the criteria1 parameter is the logical statement that is used to assess the values in the criteria_range1 parameter. This parameter is required.
[criteria_range2, criteria2 .....]the [criteria_range2, criteria2 .....] parameters are optional pairs of parameters. You can specify up 1 to 127 pairs of parameters

Some Examples

OK so what does all this all mean and what can it be used for? In the first example below we use two ranges and two criteria. The formula in this example finds the total income for males, criteria 1 highlighted in yellow, over the age of 40, criteria 2 highlighted in orange.

SUMIFS Function Example 1

The formula used in this example is:

=SUMIFS(C2:C21,A2:A21,”=Male”,B2:B21,”>40″)”)

Let’s break this formula down. The formula adds up the income from the rows in the range C2:C21 where;

  • range1 A2:A21 (highlighted in yellow) equals Male,
  • and range2 B2:B21 (highlighted in orange) is over 40,
  • the result  is £375,000 highlighted in green.

The second example uses three criteria. In this example, the SUMIFS function is used to sum all the sales figures in June (criteria 1), for Joan (criteria 2) on the sale of laptops (criteria 3).

SUMIFS Function Example 2

The formula used in example two is:

=SUMIFS(D2:D49,A2:A49,”=June”,B2:B49,”=Joan”,C2:C49,”=Laptop”)

The formula adds the total sales value for rows in the range ‘D2:D49’ where;

  • range1 A2:A49 (highlighted in green) equals June,
  • and range2 B2:B49 (highlighted in orange) equals Joan,
  • and range3 C2:C49 (highlighted in yellow) equals Laptop,
  • the result is $7500

For more information on the SUMIFS function please click here.

I hope you found this blog post useful. Please feel free to make comments, ask questions or provide feedback in the comments box below.

If you liked this post, please share it with your friends on Facebook.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit