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 on Excel (Table of Contents)**

## 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:

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 1^{st}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.

#### 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

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 is to be applied. 2^{nd} parameter is “TV” which means look for cells having value as ‘TV’ in range B2:B11. The 3^{rd} 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

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 is to be applied. 2^{nd} parameter is “” which means look for cells having blank values in range B2:B11. The 3^{rd} 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

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 is to be applied. 2^{nd} parameter is “<>” which means look for cells with NO blank values in range B2:B11. The 3^{rd} 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.

** **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 is to be applied. 2^{nd} 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 3^{rd} 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 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 to 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 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 –

- Excel SUM Function | Shortcut Keys
- SUMIF With VLOOKUP | Explanation
- Excel SUMIF Between Two Dates
- IF Formula in Excel | Examples
- Product Function in Excel
- LEFT Excel Function
- RIGHT in Excel
- SUM in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion