The SUMIF function is used for summing a range of values provided that they meet a condition that you specify in the criteria argument. The SUMIF function only works if you are baseing your summing on a single criterion. If you need to base it on more than one criterion you will need to use the SUMIFS function.
The Syntax for the SUMIF function is:
Range is the cells that you are baseing your criterion on. The range contains the values that will be checked by the criteria to determine if they are to be included in the sum.
Criteria is the condition that must be met if a value is to be included in the sum. If the condition contains any text, logical or mathematical symbols it must be enclosed in double quotation marks. Quotation marks are not necessary if the criterion is just a number.
Sum_Range is optional. If this optional range is used then it will be this range that contains the cells to be summed rather than the range in the first argument. The criteria will still be based on the first range argument.
SUMIF(range, criteria, [sum_range])Argument Descriptions:Range is the cells that you are baseing your criterion on. The range contains the values that will be checked by the criteria to determine if they are to be included in the sum.
Criteria is the condition that must be met if a value is to be included in the sum. If the condition contains any text, logical or mathematical symbols it must be enclosed in double quotation marks. Quotation marks are not necessary if the criterion is just a number.
Sum_Range is optional. If this optional range is used then it will be this range that contains the cells to be summed rather than the range in the first argument. The criteria will still be based on the first range argument.
The figure below contains a range of values that need to be summed, but we only want to sum those items whose value is greater than 900. There are only two values greater than 900 in the range B1:B6; they are 1100 and 1500.
The arguments used in the formula for summing the values greater than 900 are:
- B1:B6 – this is the Range of values that the criteria will be based on.
- ">900” – this is the Criteria. Since the criterion contains something other than just a number it must be enclosed in double quotes.
=SUMIF(B1:B6,">900")Placing the formula in cell B7 displays 2600 which is the result of adding 1100 + 1500.
This figure shows a list of items and their prices. We only want to sum those items whose price is exactly 99 cents. The formula entered into cell B10 is =SUMIF(B2:B8,0.99). Since we are only summing those values which are exactly .99 there is
no need for any additional logical symbols because the equal sign is optional. Because the criterion is only a number it does
not need to be placed within double quotes.
|
 |
Using the Optional Sum_Range
When using the optional Sum_Range argument you base your criteria on a different range of cells than those that you are summing.
We want to find the total number of males and females in a high school. The figure below shows the worksheet for this. The worksheet uses separate formulas for determining the number of males and females. The arguments used in the formula for determining the number of males are:
- B2:B9 – this is the Range that the criteria will be based on.
- "MALE” – this is the Criteria
- C2:C9 – this is the Sum_Range. The range of the cells that will be summed. if the Criteria is met.
=SUMIF(B2:B9,"M",C2:C9)
The only difference in the formula for computing females is the criteria which is “F”. The formula entered in cell C12 is =SUMIF(B2:B9,"F",C2:C9)
Use SUMIF to sum items that meet one of multiple conditions
You have just seen how SUMIF can be easily used to sum only male or only female students, but what if you wanted to know the total number of Freshman and Juniors. You can test a single field for more than one criteria by using an array. The Array is a list of the criteria values. These values are enclosed within curly brackets { }. Since we want to count students from the Freshman and Junior classes, these values will be our Criteria and we will enclose each one within quotation marks separated by a comma. When using an array within the SUMIF funtion, the SUMIF function must be enclosed within a SUM function.
The arguments used in the formula for determining the number of Freshman and Junior class members are:
- A2:A9 – this is the Range that the criteria will be based on.
- "Freshman”, "Junior" – this is the Criteria list of acceptable values
- C2:C9 – this is the Sum_Range. The range of the cells that will be summed. if the Criteria is met.
SUMIFS - Adds the Cells that Meet Multple Criteria
The SUMIFS function adds the Cells that Meet Multiple Criteria. The SUMIFS function is similar to the SUMIF function except that the SUMIFS function is used to sum values only if multiple conditions are met rather than a single condition.
The Syntax for the SUMIFS function is:
Argument Descriptions
Sum_Range is the cells that you are basing your criterion on. The range contains the values that will be checked by the criteria to determine if they are to be included in the sum.
Criteria_range1 is the first range in which to evaluate the associated criteria .
Criteria1 is the condition that defines which cells in the criteria_range1 argument will be added. Criteria_range2, Criteria2,… You can add as many as 126 additional ranges and their associated criteria.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, Criteria2]…)
Argument Descriptions
Sum_Range is the cells that you are basing your criterion on. The range contains the values that will be checked by the criteria to determine if they are to be included in the sum.
Criteria_range1 is the first range in which to evaluate the associated criteria .
Criteria1 is the condition that defines which cells in the criteria_range1 argument will be added. Criteria_range2, Criteria2,… You can add as many as 126 additional ranges and their associated criteria.
The figure below shows a company’s bank deposits along with the date of each deposit for its different branches. We want to sum the bank deposits in column B for those that meet the following conditions.
- It is not a Hobart office
- The amount of the deposit has to be at least 20,000
- The date of the deposit has to be 03/12/2010 or later
The formula that will add the deposits that meet our criteria is:
=SUMIFS(B2:B11,A2:A11,"<>Hobart",B2:B11,">=20000",C2:C11,">=03/01/2010")
The following gives a break down of the SUMIFS function:
-
The following argument specifies the range that contains the values we want to sum if they meet our criteria.
Argument: sum_range- B2:B11 -
We only want the branches that are not Hobart.
Argument: criteria_range1- A2:A11
Argument: criteria1- “<>Hobart” -
The amount of the deposit has to be at least 20,000
Argument: criteria_range2- B2:B11
Argument: criteria2- “>=20000” -
The date of the deposit has be 3/12/2010 or later
Argument: criteria_range3 – C2:C11
Argument: criteria3- ">=03/12/2010"
The rows that meet all the criteria are highlighted in the worksheet below. They are the only deposits that meet all three criteria of (1) Not being a Hobart branch, (2) Having a deposit greater than or equal to 20000 and (3) deposited on or after 3/12/2010. The result of adding the deposit for these three rows is 83562.79
