Excel SUMIF Function – An introduction
Excel SUMIF Function sums the values in a range with one criteria or condition. In this blog post, we are going to learn more about Excel SUMIF Function. SUMIF is a combined function of Excel SUM and IF function. SUM function sums the values in a range and IF function supply the criteria or condition for the same. More we will learn in this tutorial further.
Why Use Excel SUMIF Function?
SUMIF is a useful function which helps to sum the values in a range with a single condition or criteria.
What are the arguments for SUMIF?
The arguments of the function are as follows:-
=SUMIF (range, criteria, [sum_range]
Range – (required) this is the range of cell where we will apply the criteria.
Criteria – (required) this criteria or condition will determine, which cell to add.
[sum_range] [optional] – this is the actual range to sum, if omitted, it will use the first range as sum_range.
It returns a result as the sum of all number values met with the specified criteria.
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 (*) 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 ignored.
Criteria should be not more than 255 characters.
Excel SUMIF Function- where sum_range is omit
We will use below data-set to understand Excel SUMIF Function. First of all, we learn that if the third argument ‘sum_range’ is omitted, then how to apply this function.
Make a sum for the values are “In-Stock” column and must be higher than (>20) only.
First of all, in the dataset mentioned above, find out column “In Stock” and its range. Now apply Excel SUMIF Function within that range.
Know our ‘range’ and ‘criteria’ first.
Here “In-Stock” column our range is “D2: D11”.
And our criteria is “>20”.
Put these parameters “range” and “criteria” in the Excel SUMIF Function where sum_range is omitting.
=SUMIF (D2: D11,”>20”) and hit enter.
Our result is 193. See in the image:-
Sum of values which are “>20” is 193 in the “In-Stock” column.
Furthermore, we will learn where we have a third parameter sum_range also.
Excel SUMIF Function – with all parameters.
Question 2: – How to find the total values of “Oranges” in the below dataset?
First of all, within the given-dataset, we need to know our parameters within the question, focus on two things first:-
- Total sum – our total values for all the products are in the 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, 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 criteria_range will be “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)
Criteria_Range = B2:B11
Criteria = “Orange” and
Sum_range = E2:E11 (total value)
Put these parameters within the below Excel SUMIF Function. See image below:-
=SUMIF (B2:B11,”Orange”, E2:E11) and hit enter
Our result is = 4200.
Now move to the next situation.
Excel SUMIF Function – where date as criteria
Question 3: – Find out the total values of all the product where the date range is “>12/25/2014”.
First of all, try to understand the question, it is asking for the total values of all the products. But the condition is, the date must be higher than “>12/25/2014”.
In Excel SUMIF Function, criteria and criteria_range both belong to the same range and same column.
Here the criteria and its range are, “>12/25/2014” and “A2: A11″, respectively.
We have identified two parameters:- ‘criteria_range’ and ‘criteria.’
Third parameters Sum_range is, where the values of all the products are existing. i.e., “E2: E11”.
So our third argument, sum_range is “E2: E11”.
Now put these arguments within the Excel SUMIF function, see below:-
Criteria_range =”A2: A11.”
Sum_range = E2:E11 (total values)
Apply these three arguments within the Excel SUMIF Function.
=SUMIF (criteria_range, criteria, sum_range)
=SUMIF (A2: A11, “>12/25/2014”, E2: E11) and press enter.
And our result is 12517.
So, in this blog post, we learn Excel SUMIF Function with the help of three examples, and I hope now there is no doubt remaining. If there is any doubt remaining, please feel free to write a comment below.