Excel SUMIFS Function sums the values of a column in a dataset, where result meets or fulfils with multiples of condition or criteria. Microsoft has introduced this Function in 2007 and available after all the versions.
What is the difference between Excel SUMIF and SUMIFS function?
Excel SUMIF Function make a sum of values in a dataset with a single condition.
Excel SUMIFS Function makes a sum of values in a dataset with multiple criteria or condition.
Combining “SUMIF + F” creates a new SUMIFS function which is more advanced and powerful.
The Syntax for SUMIFS
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2….])
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 result
As a result, this function returns a numeric value.
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 does 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 question mark or asterisk. (i.e. ~?, ~*).
In SUMIFS function sum_range is the first argument and third argument in SUMIF function.
This function behaves “APPLE” and “apple” as equal, so not a case-sensitive.
Rows and Columns must be equal in the criteria_range and the sum_range.
We can change the position of criteria’s, 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:-
See some examples below:-
Excel SUMIFS Function with Two Criteria’s
How to find the total sales value of “Banana’s” where the zone is “South”?
In our previous post, SUMIF Function, we use only single criteria. But in this post, we are going to use multiples.
Our question is, how to find the total sales value where 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, 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’s are appearing.
We have found all the criteria’s and its 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 “South” zone is 500.
Excel SUMIFS Function with Date Criteria
How to find the sales value of the fruits, sold under “South” zone and where date range is “>=12/25/2014”?
As we did in our previous example, find out the criteria’s and criteria_ranges first.
Our criteria1 is “South” under 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.
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 “South” zone where date range “>=12/25/2014” is 782.
Excel SUMIFS Function – Greater Than and Less Than
How to find the total sales value of “Banana” where date range is “>=12/25/2014” to “<=02/15/2016”.
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’s and criteria_ranges.
According to the above question, our criteria’s 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 image below:-
Criteria_range2 and _range3 are the same, but their criteria’s are different because of 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.