SUMIF Excel Function

SUMIF in Excel

The SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12.

SUMIF function is categorized under the Excel Math and Trigonometry functions. Moreover, this function works similar to the SUMIFS functionSUMIFS FunctionIn Excel, SUMIFS is an in-built function that calculates the sum of all those cells which meet the multiple specified criteria & it is widely used as a conditional statement. read more. The SUMIF function uses a single criterion, whereas the SUMIFS function uses multiple criteria.

SUMIF Formula in Excel

The SUMIF formula is stated as follows:

SUMIF Formula

The formula accepts the following arguments:

  • Range: It refers to the range of cells on which the criteria is applied.
  • Criteria: It refers to the conditions that are applied to the range of cells. It determines the cells to be added from the given “sum_range.”
  • Sum_range: It indicates the range of cells to be added together.

“Range” and “criteria” are the required parameters, whereas “sum_range” is an optional parameter.

Note: If “sum_range” is not specified, the SUMIF function refers to the parameter “range” as the range of cells to be added.

How to Use the SUMIF Excel Function?

Let us understand the SUMIF excel function with the help of the following examples. Each example covers a different case, implemented using the SUMIF function.

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

Example #1

Total Amount Spent on Branded Televisions (TVs)

The following table shows a list of branded equipment and their prices. For some equipment, the brands are not specified.  We have to calculate the total amount spent on purchasing branded TVs using the SUMIF function. 

In the succeeding table, the cells that satisfy the given criteria are C4, C7, and C10, which shows the prices of the branded TVs. Hence, the sum of the values of cells C4, C7, and C10 is 1,73,000, shown in cell C14. This is the total amount spent on purchasing branded TVs.

The following SUMIF formula is applied to the range C2:C11:

“=SUMIF(B2:B11,“TV”,C2:C11)”

Where,

  • B2:B11 refers to the range of cells on which the specific criterion is to be applied.
  • “TV” refers to the condition applied to the range B2:B11.
  • C2:C11 refers to the range of values to be added.
SUMIF Example 1

Example #2 

Sum of the Amount Spent on Non-Branded Items

Working on the data of example #1, we want to calculate the total amount spent on purchasing non-branded items using the SUMIF function.

In the following table, the cells C11 and C12 satisfy the given criteria where no brand name is entered in the cells. Hence, the sum of the values of cells C11and C12 is 53,000, as shown in cell C14. This indicates the total amount spent on purchasing non-branded items.

The following SUMIF formula is applied to the range C2:C12:

“=SUMIF(A2:A12,“”,C2:C12)”

Where,

  •  A2:A12 refers to the range of cells on which the specific criterion is to be applied.
  • “” refers to the condition which checks for blank cells in the range B2:B12.
  • C2:C12 is the range of values to be summed up.
SUMIF Example 2

Example #3

Sum of the Amount Spent on Branded Items

Working on the data of example #1, we want to calculate the total amount spent on branded items using the SUMIF function.

In the following table, the cells B11, B12 satisfy the given criteria with no brand names. Hence, the sum of the values of C1 to C10 is 53,000, displayed in cell C14. This is the total amount spent on purchasing branded items. The cells C11 and C12 are skipped because they are blank cells.

The following SUMIF formula is applied to the range C2:C12:

“=SUMIF(A2:A12,“<>”,C2:C12)”

Where,

  • A2:A12 refers to the range on which the criteria are to be applied.
  •  “<>” is the condition which checks for non-blank cells in the range B2:B12.
  • C2:C12 is the range of values to be added.
SUMIF Example 3

Example #4 

Sum of the Two Different Items

Working on the data of example #1, we want to calculate the sum of two different items using the SUMIF formula.

The following table shows the total amount calculated by adding the sum of the prices of two items – TV and fridge in the cell C14, using the SUMIF formula. The SUM of the prices of the fridges and TVs are 79,000 and 1,98,000,  respectively. Hence, the total SUM of the prices of two items is 2,77,000.

The SUMIF formula is applied to the range C2:C12 and is stated as follows:

“=SUM(SUMIF(B2:B12,{“TV”,“Fridge”},C2:C12))”

Where,

  • B2: B12 refers to the range on which the criteria are to be applied.
  • “TV,” “fridge” refers to the two individual conditions to be checked in range B2: B12. The curly braces {} of the criterion represent the collection of constants.
  • C2: C12 refers to the range of values to be added.

In the formula, the SUMIF function is enclosed within the SUM function. The SUMIF formula executes two different conditions, “TV” and “fridge.” Finally, the SUM function sums the results of the SUMIF functions to return the output.

SUM of two different condition

The “Criteria” Argument of the SUMIF Function

The guidelines of the argument of the SUMIF formula are stated as follows:

  • The numeric criteria are not enclosed in double quotes.
  • The text criteria for SUMIF, including the mathematical symbols, are enclosed within double quotes.
  • The wildcard characters – question mark (?) and asterisk (*) used in the criteria match a single character and a series of characters, respectively.
  • The tilde operator (~) followed by the wildcard characters refer to the actual characters. For example, the “~?” indicates criteria as a question mark literally.
  • The SUMIF formula returns “#VALUE! error” when the criterion is a text string that is greater than 255 characters.

Frequently Asked Questions (FAQs)

1. What is the SUMIF function in Excel?

The SUMIF function returns the sum of cells that satisfy the given criteria. This worksheet (WS) function is categorized as a Math and Trigonometry function. Furthermore, for partial matching, it uses logical operators and wildcards.

The SUMIF formula is stated as follows:
“=SUMIF(range, criteria, [sum_range])”
Where,

• Range refers to the range of cells against which we need to apply the criteria.
• Criteria are the conditions that are applied to the range of cells.
• Sum_range is an array of numeric values. The values are added if the specific criterion matches the range.

The “range” and “criteria” are the required arguments, and “sum_range” is an optional argument.

Note: If the argument “sum_range” is omitted, the values in the “range” will be added.

2. How to use the SUMIF Excel function for multiple criteria?

The formula for the SUMIF with multiple criteria is stated as follows:
“=SUMIF(sum_range, range 1, criteria1)+SUMIF(sum_range, range 2, criteria2)”

Where,
• Sum_range refers to the range of cells to be added.
• Range1, range 2 are the range of cells on which the criteria are to be applied.
• Criteria1, criteria 2 are the conditions applied to the respective range of cells. Alternatively, we can use the SUMIFS function to sum cells satisfying the multiple criteria.

3. Can we use the SUMIF function with text criteria?

The SUMIF excel function can be used to add cells containing text strings that meet specific criteria. In the formula, the text criteria are enclosed in double quotes.

For example, consider the below SUMIF formula:
“=SUMIF(A1:A4,“Purple”,B1:B4)”

The formula adds the values in range B1:B4 if the range A1:A4 contains the text “Purple”.

SUMIF Excel Function Video

 

Recommended Articles

This has been a guide to SUMIF Excel Function. Here we discuss how to use SUMIF Formula along with excel examples and downloadable 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 *