Excel SUMIFS Function

HomeExcel FunctionsExcel SUMIFS Function (Example + Sample File)

What is EXCEL SUMIFS FUNCTION

The Excel SUMIFS Function is listed under Microsoft Excel's Maths Functions category. It returns the sum of the numbers which meet multiple conditions you specify. Unlike SUMIF, you can set multiple conditions and can sum only those values which meet all those conditions.

How to use it

To learn how to use the SUMIFS function in Excel, you need to understand its syntax and arguments: 

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

  • [sum_range]: A range of cells & values which you want to sum.
  • criteria_range1: A range from which you want to test criteria.
  • criteria1: A criteria which can be a number, text, expression, cell reference or a function.
  • [criteria_range2]: A range from which you want to test criteria.
  • [criteria2]: A criteria which can be a number, text, expression, cell reference or a function.

Notes

  • The size of the criteria range and sum range should be of the same size.
  • You can also use wildcard characters.
  • If you want to sum values based on single criteria, you can use SUMIF.
  • If you want to specify criteria in the form of text, you have to use double quotation marks.
  • If you skip specifying SUM range it will SUM the values from the criteria range that meet the criteria.

Example

To master the SUMIFS function we need to try it out in an example and below is one which you can try out:

In the below example, we have used three different criteria to check and it has returned the sum of the values which meet those criteria.

In the below example, we have used wildcard characters to specify three different criteria.

  • The first criteria range is A2:A9 and criteria in the cell A12 which will only sum cells that have alphabet O.
  • The second criterion range is B2:B9 and criteria in cell B12 which will only sum cells that have alphabet P. It will apply to all the cells which met condition 1.
  • The third criterion range is C2:C9 and criterion in cell C12 which will sum cells with any type of value. It will apply to all the cells which met the condition 1 and 2.

Related functions

This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.