SUMIF Function | Excel

Excel provides conditional functions which allow the user to add all the cells in a range that meets single or multiple criteria. The following paragraphes will explain two types of these functions called "Sumif" and "Sumifs". They could check single and multiple criteria, respectively. This tutorial assumes you have a working knowledge of Sum & If functions.

Sumif

The Sumif function or statement is used to add all the cells in a range that meets single criteria. Typically, this function consists of three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=SUMIF(range,criteria,sum_range)

Where,

• ● RANGE is the range of cells you want to test.
• ● CRITERIA. It is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
• ● SUM RANGE. These are the actual cells to sum. The cells in sum range are summed only if their corresponding cells in range match the criteria. If sum range is omitted, the cells in range are summed.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of math & trig functions. The Function Library allow to view the above syntax (details) of SUMIF function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MATH & TRIG button and select SUMIF from the displayed functions.
• 
  
  
  
• iii. The next dialog box will appear and the three arguments can be seen within the box.
• 
  
  
  
• iv. Click in SUM_RANGE text box.
• v. Select the range of cells containing the values you wish to sum up.
• vi. Click in the RANGE box select a range of cells that contains the values you wish to check the criteria against.
• vii. Click in the CRITERIA text box and type in the criteria to check against your RANGE.
• ☺ The above INSERT FUNCTION tool dialog show any errors in entering the values or ranges.

In next example, the Total of Viking Supplies could be calculated using the shown formula. Where, the entry in the Customer column should match only Viking Supplies.

Sumifs

The Sumifs function or statement is used to add all the cells in a range that meets multiple criteria. The order of arguments is different between SUMIFS and SUMIF. In particular, the SUM_RANGE argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order. Typically, this function consists of more than or equal to three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Where,

• ● SUM_RANGE is one or more cells to sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• ● CRITERIA_RANGE1, CRITERIA_RANGE2, … are 1 to 127 ranges in which to evaluate the associated criteria.
• ● CRITERIA1, CRITERIA2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "balls", or D6.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of math & trig functions. The Function Library allow to view the above syntax (details) of SUMIFS function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MATH & TRIG button and select SUMIFS from the displayed functions.
• iii. The next dialog box will appear and the three arguments can be seen within the box.
• 
  
  
  
• iv. Click in SUM_RANGE text box.
• v. Select the range of cells containing the .values you wish to sum up.
• vi. Click in the CRITERIA_RANGE1 box select a range of cells that contains the values you wish to check the criteria against.
• vii. Click in the CRITERIA1 text box and type in the criteria to measure against your CRITERIA_RANGE1.
• viii. Repeat steps v and vi to enter multiple criteria, range2, range3 etc, as you use each CRITERIA_RANGE and criteria more text boxes will appear for you to use. Click OK when all ranges and criterias have been entered.

Some important points about SUMIFS:

• ☺ Each cell in SUM_RANGE is summed only if all of the corresponding criteria specified are true for that cell.
• ☺ Cells in SUM_RANGE that contain TRUE evaluate as 1, while cells in SUM_RANGE that contain FALSE evaluate as 0 (zero).
>
• ☺ Unlike the range and criteria arguments in the SUMIF function, in SUMIFS each CRITERIA_RANGE must be the same size and shape as SUM_RANGE.

In next example, the total amounts from each bank account are calculated matching multipile criteria in terms of different interset for two years.

The Use of Wildcards Characters in Criteria Arguments

The SUMIF function supports wildcards characters. An asterisk (*) means "one or more characters", while a question mark (?) means "any one character". The asterisk character (*) can be used before, after and surrounding criteria to allow partial search criteria to be used. The question mark character (?) is used to represent the position within the criteria where any character is allowed – please see examples below:

• ☺ "WER*" Starts with WER
• ☺ "*WER" Ends with WER
• ☺ "*WER*" Contains WER
• ☺ "W?R" Contains 1 any character in the ? position
• ☺ "*W??R?" Ends with W, R & 3 any characters in the ? positions

Because asterisks and question marks are themselves wildcards, if you want to search for these characters specifically, you'll need to escape them with a tilde (~). The tilde causes Excel to treat the following character literally ("~*" to match a literal asterisk) – please see examples below:

• ☺ "~**" Starts with *
• ☺ "*~*" Ends with *
• ☺ "*~**" Contains *

For the next Example, we need to calculate the total quantity of product the matches multiple criteria using wildcards characters.



The next formula is used with three criteria:

=SUMIFS($B$2:$B$6,$A$2:$A$6,"*-T",$A$2:$A$6,C9&"*",$B$2:$B$6,">350")

• ● First criteria: sums everything but those codes ending with –T: the criteria is “<>*-T”.
• ● Second criteria: specifies the product category we want to sum. The product categories have been classified in codes and listed in column A. We are looking for product codes that start with these three characters, value held in cell C9: the criteria is C9&”*”.
• ● Third criteria: simply specifies that we only want to sum product codes where the quantity is greater than 350. No wildcards are needed for this criteria.