how to use excel sumif function

Quick Intro

Use Excel SUMIF Function to SUM numbers from a range which meets specified criteria. In simple words, you can define a range to check for criteria & a corresponding range of values which you want to sum.

Syntax

SUMIF(range, criteria, [sum_range])

  • 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 which has the values you want to sum.

More Information on Excel SUMIF Function

  • The size of criteria range & SUM range does not have to be same. It will always consider cells according to criteria range.
  • You can also use wildcard characters in the sumif function.
  • You can only able to sum values on the basis of a single criterion. And, if you want to define more than one criteria you can use SUMIFS Function for that.
  • 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 criteria range which meet the criteria.
  • You can only specify criteria up to 255 characters.

Examples

In the below example, we have specified A1:A9 as criteria range and B1:B9 as sum range. After that, I have specified the criteria in A12 which has the value C. And, the total for the product C is 20 units (12+8).

Examples For Excel SUMIF Function

You can also insert criteria directly into the function.

As I have already mentioned that you can use a wildcard character in the sumif function. In above example, I have used an asterisk to specify a criterion which has an alphabet “S”.

Examples For Excel SUMIF Function With Wildcards

If you skip specifying the sum range it will give you the sum of criteria range. But, that will only possible if criteria range has numeric values.

Examples For Excel SUMIF Function Without Sum Range

Sample File

download sample file to learn more about this tips

What’s Next?

To learn more about Excel SUMIF Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.There is also you have

There is also you have SUMIFS function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.