Sometimes, you may want to sum a range of numbers based on more than one condition. For example, you may want to calculate the total sales of a certain product in a specific region, or the total commission earned by a certain salesperson in a given month. How can you do this in Excel?
There are two main functions that can help you achieve this task: SUMIF and SUMIFS. In this article, we will explain the difference between these two functions, how to use them, and some examples to illustrate their usage.
SUMIF Function
The SUMIF function is used to conditionally sum values based on a single criterion. The syntax of the SUMIF function is:
=SUMIF(range, criteria, [sum_range])
- range: the range of cells to be evaluated by the criterion, required.
- criteria: the condition that must be met, required. You can supply the criteria in the form of a number, logical expression, cell reference, text, or another Excel function.
- sum_range: the cells to sum if the condition is met, optional. If omitted, the range argument is used as the sum_range.
For example, suppose you have a table of sales data like this:
| Region | Salesperson | Product | Sales |
|---|---|---|---|
| North | Alice | Apples | 100 |
| North | Bob | Bananas | 200 |
| South | Charlie | Cherries | 300 |
| South | David | Dates | 400 |
If you want to sum the sales of Apples in the North region, you can use this formula:
=SUMIF(A2:A5,“North”,D2:D5)
This formula will check the range A2:A5 for the value “North”, and sum the corresponding values in the range D2:D5. The result is 100.
SUMIFS Function
The SUMIFS function is used to conditionally sum values based on multiple criteria. The syntax of the SUMIFS function is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- sum_range: the cells to sum, required. This can be a single cell, a range of cells, or a named range. Only cells with numbers are summed; blank and text values are ignored.
- criteria_range1: the first range to be evaluated by the associated criterion, required.
- criteria1: the first condition that must be met, required. You can supply the criteria in the form of a number, logical expression, cell reference, text, or another Excel function.
- criteria_range2, criteria2, …: additional ranges and their associated criteria, optional. You can specify up to 127 pairs of criteria ranges and criteria in one formula.
For example, using the same table of sales data, if you want to sum the sales of Apples and Bananas in the North region, you can use this formula:
=SUMIFS(D2:D5,A2:A5,“North”,C2:C5,{“Apples”,“Bananas”})
This formula will check the range A2:A5 for the value “North”, and the range C2:C5 for the values “Apples” or “Bananas”, and sum the corresponding values in the range D2:D5. The result is 300.
Examples
To illustrate the usage of the SUMIF and SUMIFS functions, let’s create a scenario with some real data. Suppose you are a sales manager and you have a table of monthly sales data for your team like this:
| Month | Region | Salesperson | Product | Sales | Commission |
|---|---|---|---|---|---|
| Jan | North | Alice | Apples | 1000 | 10% |
| Jan | North | Bob | Bananas | 1500 | 15% |
| Jan | South | Charlie | Cherries | 1200 | 12% |
| Jan | South | David | Dates | 1800 | 18% |
| Feb | North | Alice | Apples | 900 | 10% |
| Feb | North | Bob | Bananas | 1600 | 15% |
| Feb | South | Charlie | Cherries | 1300 | 12% |
| Feb | South | David | Dates | 1700 | 18% |
| Mar | North | Alice | Apples | 1100 | 10% |
| Mar | North | Bob | Bananas | 1400 | 15% |
| Mar | South | Charlie | Cherries | 1250 | 12% |
| Mar | South | David | Dates | 1750 | 18% |
You want to use Excel to answer some questions about your sales performance, such as:
- What is the total sales of Apples in the North region in the first quarter?
- What is the average commission earned by Charlie in the South region in February and March?
- How many salespersons achieved more than 1500 sales in a single month?
To answer these questions, you can use the SUMIF and SUMIFS functions as follows:
- To calculate the total sales of Apples in the North region in the first quarter, you can use this formula:
=SUMIFS(E2:E13,A2:A13,“<=Mar”,B2:B13,“North”,D2:D13,“Apples”)
This formula will sum the values in the range E2:E13 (Sales) if the corresponding values in the range A2:A13 (Month) are less than or equal to “Mar”, the values in the range B2:B13 (Region) are equal to “North”, and the values in the range D2:D13 (Product) are equal to “Apples”. The result is 3000.
- To calculate the average commission earned by Charlie in the South region in February and March, you can use this formula:
=AVERAGE(SUMIFS(F2:F13,A2:A13,{“Feb”,“Mar”},B2:B13,“South”,C2:C13,“Charlie”))
This formula will sum the values in the range F2:F13 (Commission) if the corresponding values in the range A2:A13 (Month) are equal to “Feb” or “Mar”, the values in the range B2:B13 (Region) are equal to “South”, and the values in the range C2:C13 (Salesperson) are equal to “Charlie”. Then, it will divide the sum by the number of values that meet the criteria, which is 2. The result is 0.12, or 12%.
- To count the number of salespersons who achieved more than 1500 sales in a single month, you can use this formula:
=COUNTIF(SUMIFS(E2:E13,A2:A13,A2:A13,B2:B13,B2:B13,C2:C13,C2:C13),“>1500”)
This formula will use the SUMIFS function to create an array of sums for each combination of month, region, and salesperson. For example, the first element of the array will be the sum of sales for Alice in the North region in January, which is 1000. Then, it will use the COUNTIF function to count how many elements of the array are greater than 1500. The result is 4.
