As the name suggests, the SUMIF and SUMIFS formulas combine the SUM and IF functions. In plain English, this implies that these functions can add items or cells that meet specific criteria.

Please understand in more detail:

You know the SUM function can add items, and the SUMIF function extends the capabilities of the SUM function by letting you choose SUM only those items that meet specific criteria.

Simultaneously, the SUMIFS function is a relatively newer function (introduced with Excel 2007) that expands a SUMIF function’s capabilities by giving you the basis for adding only items that meet a set of criteria.

SUMIF function in Excel:

According to Microsoft Excel, SUMIF is defined as a function “Add cells specified by a certain condition or criteria”.

The syntax of the SUMIF function is as follows:

= SUMIF (range, criteria [, sum_range])

Here, ‘ range ‘ refers to the cells you want to be evaluated with the ‘ criteria ‘.

‘ criteria ‘ refers to the condition that determines which item will be added. The ‘ criteria ‘ can be a number, expression, or text string.

‘ sum_range ‘ is an optional argument. It defines the cells to be added. If the ‘ 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:

Goal: Our goal is to find the total amount of sales earned in the North area.

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

Range: In ” range “, select the cells that contain your criteria.

Criteria: In the parameter ” criteria” enter your criteria, i.e., ” North ” Region. Please note that writing ” = North ” is the same as writing ” North “.

sum_range: In ” sum_range “, select the cells to add after evaluating the criteria.

After applying this formula, the results appear as follows: 490688 is the addition of D3, D7, and D10.

Some examples of SUMIF:

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

Example 1: Suppose we have a table like below, and now our goal is to find the total number of units sold in the East region.

To find the solution, we can apply a formula like this: = SUMIF (B3: B11, “East”, C3: C11)

This formula results in 129

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

In this case, we can apply the following formula: = SUMIF (A3: A11, ”

Example 3: In this example, we have tables from several schools at local events. Here, our task is to find the total number of awards won by School C in all the events.

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

This formula results in 24 as D5 + D7 + D9.

SUMIFS function in Excel:

In the above examples, we have seen how to use the SUMIF function. Now let’s move to the SUMIFS function. As I said before, SUMIFS is an extension of the SUMIF function. It can add items from a range that meet multiple conditions.

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

The syntax of the SUMIFS function is as follows:

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

Here, ‘ sum_range ‘ identifies the cells that need to be added based on certain conditions. It is a required field.

‘ criteria_range1 ‘ specifies the first range, where the first criteria will be evaluated.

‘ criteria1 ‘ specifies the condition to be evaluated in ‘ criteria_range1 ‘.

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

How to use SUMIFS in Excel:

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

Consider we have a sales table like the image below.

Goal: Our goal is to find the total number of units sold from North after December 31, 2011.

So we will try to apply SUMIFS functions like:

‘sum_range’: In sum_range ”, select the range whose item you want to add.

‘criteria_range1’: In the ” criteria_range1 “, select the range where the first condition needs to be evaluated. In this case, our first condition is based on the region.

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

‘criteria_range2’: In the ” criteria_range2 “, select the second range of conditions to be evaluated. In this example, our second condition will be based on the date.

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

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

Some examples of goals:

Example 1: In this example, we have a sales table again, and the task was to find the total number of sales units for salesperson ” Steve ” in 2012.

We can apply the SUMIFS formula in this case as follows: = SUMIFS (D3: D11, C3: C11, “Steve”, A3: A11, “> 31/12/2011 ″)

This formula yields 907.

Example 2: In this example, we will try to find the total incremented from the “ West ” area, where items are sold more significantly than 500.

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 measurement table like below. Our task is to find the total Rainfall for all days where the humidity is greater than 65, and the temperature is greater than 40.

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

This gives 38 as a result, which is an item B4 and D4.

Some important points about SUMIF and SUMIFS Formulas:

  • Both SUMIF and SUMIFS formulas support wildcard characters. You can use wildcard characters (like: ‘*’ and ‘?’) In the ” criteria “.
  • In SUMIF, the cells in the ‘ range ‘ argument and ‘ sum_range ‘ do not have to be of the same size and shape. But this is not true in the case of SUMIFS.
  • You can use multiple operators (such as: “=”, “>”, “=”, “”) in criteria of both functions.

So this is all about Excel SUMIF and SUMIFS formulas. Please feel free to comment on the topic.