SUMIF Excel Function

SUMIF Excel function is a conditional sum function in excel which is used to calculate the sum from a given data sets based on a given criteria, this function is similar to SUMIFS function but the difference is that SUMIFS uses multiple conditions and SUMIF function uses a single condition, the syntax to use this function is as follows =SUMIF(Range, Criteria, Sum range).

SUMIF in Excel

It is a built-in function in MS Excel. Excel SUMIF Function is categorized under Math Functions in MS Excel. SUMIF in Excel that adds all numbers in a range of cells that meet the specified criteria. It can be used as a worksheet(WS) function in Excel, where it can be entered as a part of a SUMIF formula in Excel worksheet cells.

SUMIF Formula in Excel

The SUMIF formula is as follows:

SUMIF Formula

The SUMIF Formula in Excel has three arguments, out of which two are optional. Where,

  • range = This is a required parameter. It indicates the range of cells that the criteria are to be applied against. This is a lookup range on which the criteria are applied.
  • criteria = This is a required parameter. It is the condition to determine the cells from the given sum_range.
  • sum_range = This is an optional parameter. It indicates the range of cells to be sum together. If no value specified, it refers to the 1st parameter, i.e., range as the range of cells to be the sum.

How to Use the SUMIF Function?

The SUMIF in Excel is a Worksheet (WS) function. As a WS function, it can be entered as a part of the formula in a cell of a worksheet. Refer to the example given below to understand better.

You can download this SUMIF Function in Excel Template here – SUMIF Function in Excel Template

SUMIF in Excel Worksheet

Let’s look at the SUMIF Excel example given below. Each SUMIF in Excel example covers a different use case implemented using SUMIF in Excel.

Example #1 – Total amount spent on TV

SUMIF Example 1

In this example, cell C14 has a SUMIF Formula Excel associated with it. So, C14 is a result cell. The first argument of SUMIF in Excel is a B2:B11 is the range on which the criteria are to be applied. 2nd parameter is “TV,” which means look for cells having value as ‘TV’ in range B2:B11. The 3rd parameter is C2:C11, which the range of values to be added. There are 3 entries that satisfy the given criteria. So, the sum of the values from C4, C7, and C10 would be performed, which is 1,73,000.

Example #2 – Sum of Non-Branded Spent

SUMIF Example 2

In this example, cell C14 has a SUMIF Formula Excel associated with it. So, C14 is a result cell. The first argument of SUMIF function in Excel is an A2:A12 is the range on which the criteria are to be applied. 2nd parameter is “which means look for cells having blank values in range B2:B11. The 3rd parameter is C2:C12, which the range of values to be added. There are 2 entries that satisfy the given criteria where no brand name is entered in the cells. So, the sum of the values from C11, C12, would be performed, which is 53,000.

Example #3 – Sum of Branded Spent

SUMIF Example 3

In this Excel example, cell C14 has a SUMIF Formula Excel associated with it. So, C14 is a result cell. The first argument of SUMIF function is an A2:A12 is the range on which the criteria are to be applied. 2nd parameter is “<>,” which means look for cells with NO blank values in the range B2:B11. The 3rd parameter is C2:C12, which the range of values to be added. There are 2 entries that satisfy the given criteria where no brand name is entered in the cells. So, the sum of the values from C1 to C10 would be performed, which is 53,000. The cells C11 and C12 would be skipped as those are blank.

Example #4 – Sum of Two Different Conditions.

SUM of two different condition

 In this Excel example, cell C14 has a SUMIF Formula Excel associated with it. So, C14 is a result cell. The first argument of SUMIF function in Excel is a B2: B12 is the range on which the criteria are to be applied. 2nd parameter is “TV,” Fridge,” which means look for cells with values for TV and Fridge in range B2: B11. So, here SUMIF will be executed on two individual conditions. Such conditions need to be written inside curly braces represented by {}. It is called a collection of constants that represent the criterion. The 3rd parameter is C2: C12, which the range of values to be added. Here, SUMIF is surrounded by SUM function. It will be executed separately for two different conditions, i.e., TV and Fridge. Further, those two results would be added because of the exterior SUM function.

The SUM of the prices represented by the Fridge is 79,000, and the SUM of the prices represented by TV is 1,98,000. So, the total would be 2,77,000.

Things to Remember

  • When sum_range is not provided, it refers to the range parameter value for the range of cells to be the sum.
  • Numeric criteria need not be enclosed in quotation marks.
  • The text criteria for Sumif, including mathematical symbols, need to be enclosed in double-quotes. (“)
  • The wildcard characters such question mark (?) and asterisk (*) can also be specified in the criteria where ‘?’ is used to match a single character whereas ‘*’ is used to match any series of characters.
  • To use the above wildcard characters as literal ones, the same needs to be preceded by the tilde operator (~). E.g., ~? : – this indicates criteria as question mark itself.

SUMIF Excel Function Video

Recommended Articles

This has been a guide to SUMIF in Excel. Here we discuss the SUMIF Formula in excel and how to use it along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *