How to use the SUMIFS function in Excel

In this article, we will learn How to use the SUMIFS function in Excel.

Sum if with criteria

In simple words, while working with a long data sheet. Sometimes we need to find the sum of values having multiple criteria. SUMIFS function formula finds the SUM of the array where multiple conditions need to be satisfied. Let's learn the SUMIFS function Syntax and an example to illustrate the result.

SUMIFS Function in Excel

SUMIFS function returns the sum of range having multiple criterias.

Syntax of formula:

= SUMIFS ( Sum_range , range1 , "value1" , range2, ">value2", ….)

Sum_range : range where sum is required

range1 : range where criteria value1 is applied

Value1 : value criteria applied on range1

range2 : range where criteria value2 is applied

Value2 : value criteria applied on range2

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example.

Here we have a list of received orders and their respective values.

Here we need to find the TotalPrice sum if the region is EAST and Quantity is greater than 50.

So we will use the formula to get the total amount

= SUMIFS ( D2:D15 , B2:B15 , "East" , C2:C15 , ">50" )

D2:D15 : Sum_range where values gets sum

B2:B15 : range where criteria value1 is applied.

"East" : value criteria applied on range1.

C2:C15 : range where criteria value2 is applied.

">50" : value criteria applied on range2.

Explanation for the formula:

Here the range B2:B15 checks for the value East (Region) and in range C2:C15 catches all the quantity greater than 50. Then the function finds out the sum from the sum_range (D2:D15) taking the values satisfying the both conditions.

Here the arguments to the function is given as cell reference.

Use the formula as stated above to calculate the total price and click Enter.

As you can see the formula returns $1249.56 , the Total price for the yellow rows marked.

SUMIFS with date values

Syntax:

=SUMIFS(Sum_range , range ,">=" & date1, range, "<=" & date2)

Sum_range : range where sum is required

range : Set of dates

& : operator used to concatenate other ope

Example

Here we need to find the TotalPrice sum if date is between 2/20/2019 (after) & 7/9/2019 (before).

So we will use the formula to get the amount

=SUMIFS (F2:F10 , A2:A10 , ">=" & I3, A2:A10 , "<=" & J3)

F2:F10 : Sum_range

A2:A10 : range where condition is applied

">=" & I3 : greater than date value in I3(2/20/2019).

"<=" & J3 : less than date value in J3.(7/9/2019).

Use the formula as stated above and click Enter.

As you can see the formula returns 487.4, the Totalprice between dates.

For customization just change the dates in I3 & J3 cell and get the results with the formula.

Here are some observational results using the SUMIFS function in Excel.

Notes:

The SUMIFS function supports logical operators like <, >, <>, = but these are used using double quote sign ( " ) .
The SUMIFS function also supports Wildcards ( * , ? ) which helps in extracting values having phrases.
Non - numeric values must be provided in double quotes ("value").
The function returns the sum of the values satisfying all the conditions.

Hope this article about How to use the SUMIFS function in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

SUM if date is between : Returns the SUM of values between given dates or period in excel.

Sum if date is greater than given date: Returns the SUM of values after the given date or period in excel.

2 Ways to Sum by Month in Excel: Returns the SUM of values within a given specific month in excel.

How to Sum Multiple Columns with Condition: Returns the SUM of values across multiple columns having condition in excel

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.