Excel SUMIF Function

HomeExcel Functions List (Top 100) Examples + Sample FileExcel SUMIF Function (Example + Sample File)

What is EXCEL SUMIF FUNCTION

The Excel SUMIF Function is listed under Microsoft Excel's Maths Functions category. It returns the sum of the numbers which meet the condition you specify. In simple words, it only considers and calculates the sum of values that fulfill the condition.

How to use it

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

Syntax

SUMIF(range, criteria, [sum_range])

Arguments

  • range: A range of cells from which you want to check for criteria.
  • criteria: A criteria which can be a number, text, expression, cell reference or a function.
  • [sum_range]: A cell range that has the values you want to sum.

Notes

  • If the sum_range is omitted, the cells in range will be summed.
  • Make sure to use double quotation marks to specify Text criteria or criteria that include math symbols, must be enclosed in double quotation marks.
  • The size of the criteria range and sum range should be of the same size.

Example

To master the SUMIF 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 specified A1:A9 as criteria range and B1:B9 as sum range and after that, we have specified the criteria in A12 which has the value C.

You can also insert criteria directly into the function. In the below example, we have used an asterisk wildcard to specify a criterion which has an alphabet "S".

And, if you skip specifying the sum range it will give you the sum of criteria range.

But, that will be only possible if the criteria range has numeric values.

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.