SUMIFS Function in Excel – Multiple Condition Sum

In the previous article, we learned about the SUMIF function which finds the sum of values based on the fulfillment of a single condition in Excel. However, many a time you may want to check for multiple conditions in excel. In that case, excel has come up with another condition-based sum function called the SUMIFS function.

The name SUMIFS is made up of two words – SUM and IFS. This means that the function will find sum of numbers in excel when multiple conditions are satisfied.

Let us deep dive into this excel function 😎

When to Use The SUMIFS Function in Excel

The SUMIFS excel formula is used to find the sum/total of values in excel if the specified conditions meet or get fulfilled. This function is a member of the math/trig excel function group.

This function returns sum of values based on conditions fulfillment.

Unlike the SUMIF excel function which checks for a single condition, the SUMIFS formula considers more than one condition.

Syntax and Arguments

=SUMIFS(sum_range, criteria_range1, criteria1, ….)

Below points explains each of the arguments of the SUMIFS function:

• sum_range – In this argument, specify the cell range containing values to sum.

• criteria_range1 – In this argument, mention the range of cells on which you want to check for the criteria.

• criteria1 – In this argument, specify the first criteria to check on the criteria_range1.

You may add more than one criteria and criteria_ranges in SUMIFS function separated by a comma, like this:

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

Examples of SUMIFS Function in Excel

In this section of the article, we would learn how to use the SUMIFS function in excel with the help of some example.

Ex. 1 # Basic SUMIFS Formula Example with Single Criteria

The below image shows the zone-wise profit earned by a company during an year. Suppose you want to find the total of profit for each zone.

Since the above example involves a single criteria (viz. zone), therefore we can use the SUMIF excel function, (already learned here).

Alternatively, the SUMIFS excel formula is also useful in such case.

Use the following SUMIF formula to achieve the result.

=SUMIFS(B2:B10,A2:A10,"North")

As a result, excel returns the total of profit earned in ‘North’ region.

Note that, while using SUMIFS function, the texts like ‘North’ and so on must be put within double-quotes (” “).

Ex. 2 # Finding Sum Total of All Values That Lies Between Two Numbers

The SUMIFS function is like a life-saver formula when you want to find the sum total of all the values between two numbers.

Let see this with a help of an example.

Below image contains a list of some random numbers. Suppose you want to find the sum of all the number that lies between 60 and 80.

To do so, we shall use the SUMIFS function with two criterias, which are above 60 and below 80.

=SUMIFS(A2:A14,A2:A14,">"&D1,A2:A14,"<"&D2)

In the above example, since we are using cell reference for the lower and upper limit (and not text), there is no need to use double-quotes. The string “>”&D1 makes “>60” and “<“&D2 makes “<80”.

Additionally, instead of using the ampersand symbol (&), you may use the CONCATENATE function like this:

=SUMIFS(A2:A14,A2:A14,CONCATENATE(">",D1),A2:A14,CONCATENATE("<",D2))

The result of the above formula will be the same.

Other useful logical operator are listed below, with explanation:

Ex. 3 # Finding Sum Total of All Values That Lies Between Two Dates

Similar to what we learned in the above example, we can also find the sum total of all the numbers between two dates using the SUMIFS function.

The below image shows the sales made by a company during different dates of the year. Suppose you want to find the total of sales made during a particular month (let’s say in June, 2020).

To achieve this, we shall use the SUMIFS function, as below:

=SUMIFS(B2:B11,A2:A11,">"&E1,A2:A11,"<"&E2)

Alternatively, you may also embed the DATE function inside the SUMIFS function, like this:

=SUMIFS(B2:B11,A2:A11,">"&DATE(2020,6,1),A2:A11,"<"&DATE(2020,6,30))

The result remains the same.

With this, we have reached to the end of this article on SUMIFS formula in excel.

Thank You 🙂