How to use Excel SUMIF Function - Multiple Condition (with examples)

Here we are going to learn about Excel SUMIF Function. This is a combination of Excel SUM and IF function.

An introduction

SUM function sums the values in a range where the IF function supply the criteria or condition for the same. More we will learn in this tutorial further.

Use of Excel SUMIF Function

SUMIF is a useful function that helps to sum the values in a range with a single condition or criteria.

Arguments of SUMIF

The arguments of the function are as follows:-

=SUMIF (range, criteria, [sum_range]

Parameters Explained

range – (required) this is the range of cells where we will put the criteria.

criteria – (required) this criteria or condition will determine the condition of the sum.

[sum_range] [optional] – this is the actual range to sum, if omitted, it will use the first range as sum_range.

What it returns?

It will return a result as the sum of all number values met with the specified criteria.

Important Note

If the criteria are text or math symbols (>, <,*, /) it must be in double quotation marks (“).

Numeric criteria entered without quotation marks.

If sum_range omitted, first parameters “range” will be summed.

Wildcard character (?) and (*) can be used in criteria. Question (?) mark matches only one character where an asterisk (*) matches a sequence or number of characters.

To find question mark or asterisk, use the tilde (~) in from question mark or asterisk (i.e. ~? and ~*).

Criteria can be a number, expression, cell reference, text, or formula.

Blank cells or text values in sum_range are ignored.

Criteria should be not more than 255 characters.

Excel SUMIF Function- sum_range is omit

We will use below-given data-set to understand Excel SUMIF Function. First we learn that if the third argument ‘sum_range’ is omit in any cases then how to apply this function.

Question 1:

Make a sum for the values are stored “In-Stock” column and must be greater than (>20) only.

Solution:

In the above mentioned data-set, find out column “In Stock” and its range. Now apply Excel SUMIF Function within that range.

Know our ‘range’ and ‘criteria’ first.

So, here “In-Stock” column our range is “D2:D11”.

And our criteria is “>20”.

Now, put these parameters “range” and “criteria” in the Excel SUMIF Function. Where sum_range is omit.

=SUMIF(range, criteria,[sum_range]

See below image:-

Our formula looks like:-

=SUMIF (D2:D11,”>20”) and hit enter.

Our result is 193.

Sum of values which are “>20” is 193 in the “In-Stock” column.

Furthermore, we will learn that if we have a third parameter sum_range also.

Excel SUMIF Function – with all parameters.

When all the parameters are given, such as: – range, criteria, sum_range.

Question-2

How to find the total values of “Oranges” in the below-given dataset?

Solution:

First of all, within the given dataset we need to know our parameters within the question, focus on two things first:-

Total value – our total values for all the products are stored in column “Value”, range (E2:E11).
Oranges – this is our ‘criteria’ parameter and located in column “Fruit Name”, the range is “B2:B11”.

In this example, our parameters belong to two different ranges, so here our third argument will exist also.

See below image and identify our parameters and criteria.

Here, in this image we can identify that our values and criteria are in two different columns or ranges.

So here total values will be our sum_range, “Orange” is our criteria and range of criteria will be our criteria_range “B2:B11”.

Now put these parameters within the Excel SUMIF Function and find our desired result.

Our function is: – =SUMIF (Criteria_range, criteria, sum_range)

Where

Criteria_Range = B2:B11

Criteria = “Orange” and

Sum_range = E2:E11 (total value)

Put these parameters within the below given Excel SUMIF Function. See image below:-

=SUMIF (B2:B11,”Orange”, E2:E11) and hit enter

Excel SUMIF Function – Image -04

Our result is = 4200.

Now move to the next condition.

Question 3

Find out the total values of all the product where the date range is “>12/25/2014”.

Solution:

First, try to understand situation, it is asking for the total values of all the products. But the condition is, the date must be is greater than “>12/25/2014”.

In Excel SUMIF Function, criteria and criteria_range both belong to the same range and in the same column. Here our criteria are “>12/25/2014”, so criteria_range is the whole range where our criteria exist. i.e. “A2:A11”.

We have identified two parameters:- criteria_range and criteria.

Third parameter Sum_range is, where the values of all the products is existing. i.e. “E2:E11”.

So our third parameter, sum_range is “E2:E11”.

Now put these parameters in one place within the Excel SUMIF function, see below:-