In this tutorial, you will learn about Excel SUMIFS Function. This is an inbuilt function of Microsoft Excel and introduced in Excel 2007 and available after all the versions.
This function can find the value within a dataset with single or multiple criteria or conditions.
We have completed, Excel SUM and SUMIF Functions in our previous posts.
Contents
Syntax of Excel SUMIFS Function
=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2….])
Argument Explained
Sum_range = (required) this is the range where our result falls.
_range1= (required) this the first range of the criteria or condition
Criteria1 = (required) this is our criteria or condition
_range2 = [optional] this the range of our second criteria
Criteria2 = [optional] this is our second criteria
Returns as a Result
As a result, this function returns a numeric value.
Important note:
We can make a pair of criteria_range and criteria arguments up to 127 within this function.
Text criteria as a string (“east”) or an expression (>, <, <>, =), must be entered within the quotes.
Numeric criteria do not require double-quotes.
This function also supports logical operators (>, <, <>, =) and wildcard characters (*, ?) for partial match.
Wildcard characters can be used as criteria where (?) question mark matches the single. An asterisk (*) matches any sequence of characters.
Use the tilde (~) before a question mark or asterisk to find a question mark or asterisk. (i.e. ~?, ~*).
In the Excel SUMIFS function, sum_range is the first argument and the third argument in the SUMIF function.
This function behaves “APPLE” and “apple” as equal, so not case-sensitive.
Rows and Columns must be equal in the criteria_range and the sum_range.
We can change the position of criteria, but make sure its ranges must be changed respectively.
Here, in this post, we will use the following dataset as an example
See the image:-
With the help of the above dataset, we will use multiple examples to learn this function deeply.
Excel SUMIFS Function with Two Criteria
Situation-01
How to find the total sales value of “Banana’s” where the zone is “North”?
In our previous post, Excel SUMIF Function, we use only single criteria. But in this post, we are going to use multiples.
Solution:
Our question is, how to find the total sales value where the fruit is “Banana” and its zone is “North”.
First, find out the range where our all (sum_range) or sales values are appearing, it is range “F2: F11”. Our first parameter is final.
As per our question, the first criteria is, “North” and is appearing under the “Zone” column. So criteria1 is “North”, and criteria_range1 is “B2: B11.
Our second criteria “Banana” and is appearing under the “Fruits” column. So our criteria2 is “Banana”, and criteria_range2 is “C2: C11”.
See the below image where all the criteria are appearing.
We have found all the criteria and their ranges in the above dataset
Now, put all these parameters within the Excel SUMIFS Function.
=SUMIFS (sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
=SUMIFS (F2:F11,B2:B11,”North”,C2:C11,”Banana”) and now press enter.
Our result is =500
So, the total sales value of “Banana” under the “North” zone is 500.
Excel SUMIFS Function with Date Criteria
Situation-02
How to find the sales value of the fruits, sold under the “South” zone and where the date range is “>=12/25/2014”?
Solution:
As we did in our previous example, find out the criteria and criteria_ranges first.
Our criteria1 is “South” under the Zone column, so our criteria_range1 will be “B2: B11”, where our criteria exist.
Our criteria2 is date range “>=12/25/2014”, so our criteria_range2 will be “A2: A11” because our criteria exist here.
See image below:-
Now put these parameters within the Excel SUMIFS Function as we applied in the above image example
Our function is :- SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2,criteria2).
Now put all values within the Excel SUMIFS Function. See below:-
=SUMIFS (F17:F26, A17:A26,”>=12/25/14″,B17:B26,”South”) and press enter.
Our result is = 782.
So, the total sales value of fruits sold under the “South” zone where the date range “>=12/25/2014” is 782.
Excel SUMIFS Function – Greater Than and Less Than
Situation-03
How to find the total sales value of “Banana” where the date range is “>=12/25/2014” to “<=02/15/2016”.
Solution:
In this example, we will find out the sales value of a fruit “Banana”, which falls between two date ranges.
First of all, we need to find out our criteria and criteria_ranges.
According to the above question, our criteria are “Banana” and “date ranges”.
So, our criteria’s and its range are as follows:-
Sum_range: F33: F42 (where our sales value exist)
_range1: C33: C42 (where criteria1 falls), Criteria1: “Banana”
_range2: A33: A42 (where first date range falls), Criteria 2: “>=12/25/2014”
_range3: A33: A42 (where second date range fall), Criteria3: “<=02/15/2016”
See the image below:-
Criteria_range2 and _range3 are the same, but their criteria are different because both fall in the same range.
Now, we will apply these values within the below-given function.
Sum_range = F32:F42
_range1 = C33: C42, Criteria1 = “Banana”
_range2 = A33:A42, Criteria2 = “>=12/25/14”
_range3 = A33:A42, Criteria3 = “<=02/15/16”
Now put these values within the function with their respective places.
=SUMIFS (F33:F42, C33:C42,”Banana”, A33:A42,”>=12/25/14″, A33:A42,”<=02/15/16″) and press enter
And our answer = 1282.
So, the total sales value of “Banana”, sold between the date range “>=12/25/2014” to “<=02/15/2016” is 1282.
So here, you can see that we have applied multiple criteria to find the total sum value of the product.
If you want to learn more about this function, please visit Microsoft Office Support.
I hope you find this post useful, please feel free to put your comments and suggestion in the comment box below.
Thanks
Narendra
Related Post
Excel SUM Function – How to Use
How to use Excel SUMIF Function