Excel SUMIF and SUMIFS – Explained

As the name suggests SUMIF and SUMIFS formulae are formed by combining SUM and IF functions. In simple English this implies that these functions can add the items or cells that fulfill a particular criteria.

Let’s understand it in detail:

You know SUM function has the capability to add items, and SUMIF function extends the capabilities of SUM function by giving you the choice to SUM only those items that meet a particular criteria.

At the same time SUMIFS function is a relatively newer function (introduced with Excel 2007) that extends the abilities of a SUMIF function by giving you the facility to add only those items that meet a set of criteria.

SUMIF Function in Excel:

According to Microsoft Excel SUMIF is defined as a function that “Adds the cells specified by a given condition or criteria”.

The Syntax of SUMIF Function is as under:

=SUMIF(range, criteria [, sum_range])

Here, ‘range’ refers to the cells that you want to be evaluated by the ‘criteria’.

criteria’ refers to the condition that specifies which items are to be added. ‘criteria’ can be a number, expression or a text string.

sum_range’ is an optional argument, it specifies the cells to be added. If ‘sum_range’ argument is omitted then SUMIF treats ‘range’ as ‘sum_range’.

How to use SUMIF in Excel:

Now let’s try and understand how to apply a SUMIF formula.

Suppose we have a sales table as shown below:

How-to-use-SUMIF

Objective: Our objective is to find the Total Sales Amount earned from North region.

Let’s try to apply a SUMIF to find the solution.

Using SumIf Function

range: In the ‘range’ argument select the cells that include your criteria.

SUMIF-Function-Criteria

criteria: In the ‘criteria’ argument enter your criteria i.e. “North” Region. Please note that writing “=North” is same as writing “North”.

Using-SUMIF-Function

sum_range: In the ‘sum_range’ argument select the cells that should be added after evaluating the criteria.

SumIF Result

After applying this formula the result comes out as: 490688 which is the addition of D3, D7 and D10.

Few Examples of SUMIF:

Let’s have a look at some of the examples where SUMIF Function can be used:

Example 1: Suppose we have a table as below and now our goal is to find the total units sold in East Region.

SumIf Example 1

To find the solution we can apply a formula as: =SUMIF(B3:B11,"East",C3:C11)

This formula results into: 129

Example 2: In the same table as above, write a formula to find the total amount earned before the date “01/01/2012”.

SumIf Example 2

In this case we can apply the formula as: =SUMIF(A3:A11,"<01/01/2012",D3:D11)

Example 3: In this example we have a table of performance of some schools at local events. Here our task is to find the total number of awards won by School C in all the events.

SumIf Example 8

Here we can use a formula: =SUMIF(C3:C11,"School C",D3:D11)

This formula results into 24 which is D5 + D7 + D9.

SUMIFS Function in Excel:

In the above examples we have seen how to use SUMIF function, now let’s move to SUMIFS function. As I have foretold that SUMIFS is an extension of SUMIF function, it has the ability to add the items from a range that satisfy multiple conditions.

According to Microsoft Excel SUMIFS can be defined as a function that “Add the cells specified by a given set of conditions or criteria”.

The syntax of SUMIFS function is as follows:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Here, ‘sum_range’ specifies the cells that are to be added based on the given conditions. It is a required field.

criteria_range1’ specifies the first range where the first criteria is to be evaluated.

criteria1’ specifies the condition that is to be evaluated in the ‘criteria_range1’.

criteria_range2’, ‘criteria2’ specifies the other ranges and their respective conditions. It is an optional argument. SUMIFS supports a total of 127 range criteria pairs.

How to use SUMIFS in Excel:

To understand how to use SUMIFS function let’s take an example:

Let’s consider we have a Sales Table as shown below.

How-to-use-SUMIF

Objective: Our objective is find the total units sold from North region after the date 31/12/2011.

So, we will try to apply the SUMIFS function as:

SumIFS-How-to-Use

‘sum_range’: In the ‘sum_range’ argument select the range whose items you want to add up.

How-to-SumIfs-10

‘criteria_range1’: In the ‘criteria_range1’ argument select the range where the first condition should be evaluated. In this case our first condition is based on the region.

SumIFS-How-To-Use-11

‘criteria1’: In the ‘criteria1’ argument enter the condition that you wish to evaluate in ‘criteria_range1’. In our case the ‘criteria_1’ is “North”.

SumIFS-How-to-Use-12

‘criteria_range2’: In the ‘criteria_range2’ argument select the range where second condition should be evaluated. In this example our second condition would be based on the date.

SumIFS-Function-Critera

‘criteria2’: In the ‘criteria2’ argument enter the condition that you wish to evaluate in ‘criteria_range2’. In our case the ‘criteria_2’ is “>31/12/2011”.

SumIfs-Result

The output of this formula is 903, which is the sum of C1 and C7.

Few Examples of SUMIFS:

Example 1: In this example we again have a sales table, and out task is to find the total units sold by the sales person “Steve” in the year 2012.

SumIFS-Example-15

We can apply a SUMIFS formula in this case as: =SUMIFS(D3:D11,C3:C11,"Steve",A3:A11,">31/12/2011")

This formula gives the result 907.

Example 2: In this example we will try to find the total amount raised from “West” region where items sold is greater than 500.

SumIFS-Example-16

In this case we can use the formula: =SUMIFS(E3:E11,B3:B11,"West",D3:D11,">500")

Example 3: In this example we have a daily measurements table as shown below. Here our task is to find the total Rainfall for all the days where humidity is greater than 65 and temperature is greater than 40.

SumIFS-Example-17

To find the solution we can apply the formula: =SUMIFS(B4:D4,B3:D3,">40",B2:D2,">65")

This gives 38 as the result which is a sum of B4 and D4.

Few Important points about SUMIF and SUMIFS Formulas:

  • Both SUMIF and SUMIFS formulae support wildcard characters. You can use wildcard characters (like: ‘*’ and ‘?’) in the ‘criteria’ argument.
  • In SUMIF the cells in ‘range’ argument and ‘sum_range’ need not be of same shape and size. But this does not stands true in case of SUMIFS.
  • You can use multiple operators (like: “=”, “>”, “<”, “>=”, “<=”, “<>”) in the ‘criteria’ argument of both the functions.

So, this was all about Excel SUMIF and SUMIFS formulae. Feel free to drop your comments regarding the topic.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Comments

  1. Rahim Zulfiqar Ali says:

    Thanks Ankit for this very useful lessons on Excel. I suggest that If you can also add XLSX file to download the examples mentioned above and also post some challenges so that it would be more practical and handy to learn more. Thank You

  2. Rudra sharma says:

    I always struggled to understand sumifs syntax.But now it is clear. Thank you…

Privacy Policy | Disclaimer | About