Buzz
When summing in Excel, many people use the SUM function or the plus sign “+”. However, if you want to sum with specific conditions, you should know how to use the SUMIFS function for multiple conditions. So, how do you use the SUMIFS function to sum with 2 conditions in Excel? Let's find out.
Definition of the SUMIFS function
This is a function combining SUM and IFS in Excel, used to sum values within a certain range that satisfy specific conditions. You can also simply call the SUMIFS function the function for summing with multiple conditions.
Efficient and accurate use of the SUMIFS functionThis function is used when you need to sum values based on one or more specific conditions. For example, if you have a sales data table and want to calculate the revenue from a specific product in a particular quarter, you can use this function for a quick calculation. Additionally, you can use the function if you want to calculate the total revenue for products, with the condition that the revenue must be greater or less than a certain value.
Note: Distinguish between the SUMIF and SUMIFS functions. SUMIF allows using only one condition, while SUMIFS allows using one or more different conditions for summing.
Formula for SUMIFS function calculating multiple conditions
Before learning how to use the SUMIFS function, you need to grasp the correct syntax of the function. Specifically, here is the correct syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Where:
- Sum_range: The range to sum (cells containing values you want to add up).
- Criteria_range1: The range of cells to evaluate the first condition.
- Criteria_range2: The range of cells to evaluate the second condition.
- Criteria1: The first condition, applicable to cells within the criteria_range1 range.
- Criteria2: The second condition, applicable to cells within the criteria_range2 range.
Note: The SUMIFS function will sum values within the selected range when those values meet all the specified conditions.
Guide on how to use the SUMIFS function in Excel
The conditional sum function is widely used in Excel. To better understand how to use the function, you can follow a basic problem applying the SUMIFS function below.
Problem: Suppose you have a sales data table for the year 2023 as follows. The task is to calculate the total revenue of product A in Quarter 2 in Excel.
Example: How to use the SUMIFS function to calculate product revenueInstructions:
Step 1: In cell D2, enter the formula as follows: =SUMIFS(C2:C9, A2:A9, 'Product A', B2:B9, 'Q2').
Where:
- C2:C9 is sum_range: The range to sum (Revenue column).
- A2:A9 and B2:B9 are criteria_range1 and criteria_range2: The ranges to evaluate conditions.
- 'Product A' and 'Q2' are criteria1 and criteria2: The conditions applied to range 1 and 2, meaning finding Product A in Quarter 2.
Step 2: Press Enter and view the result in cell D2.
So, with just these 2 steps, you now know how to use the SUMIFS function in Excel. However, to better understand its practical usage, let's follow the specific examples below.
Example of using the SUMIFS function in Excel
Numerous real-world problems can benefit from the conditional sum function for faster calculations. Moreover, the usage of the function may vary depending on the requirements of each problem. Here are some real-life examples to apply this function with 1 or multiple conditions.
How to use the SUMIFS function for conditional summing
When there's only one condition, users often prefer using the SUMIF function rather than SUMIFS. However, you can also use this function if you want to calculate the sum with 1 condition in Excel.
Example: Suppose you have a simple sales data table like the following. The task is to calculate the total sales of store A using the SUMIFS function.
Illustrative example of calculating sales using the SUMIFS functionInstructions:
Step 1: In cell C2, enter the formula as follows: =SUMIFS(B2:B7, A2:A7, 'A')
Where:
- B2:B7 is the range to sum (Revenue column).
- A2:A7 is the range to evaluate the condition (Store column).
- 'A' is the condition, meaning finding store A in the list.
Step 2: Press Enter and view the result in cell C2.
How to use the SUMIFS function with 2 conditions
The function for summing with 2 conditions is the most common problem type. In these conditions, you can use comparison operators such as >, <, <=, >=, <>, and = to set the conditions.
Example problem: Suppose you have a student's grade table for various classes as shown below. The task is to calculate the total points of female students with scores higher than 8.
Instructions:
Step 1: In cell E2, enter the formula as follows: =SUMIFS(D2:D9, B2:B9, 'Female', D2:D9, '>8')
Where:
- D2:D9 is Sum_range: The range to sum (Grade column).
- B2:B9 is Criteria_range1: The range to evaluate the first condition (Gender column).
- 'Female' is Criteria1: The first condition, meaning finding female students.
- D2:D9 is Criteria_range2: The range to evaluate the second condition (Grade column).
Step 2: Press Enter and view the result in cell E2.
How to use the SUMIFS function with multiple conditions
Sometimes, you can use multiple SUMIFS functions simultaneously to calculate totals based on different sets of conditions. To better understand the usage in this case, follow the illustrative example below.
Problem: Suppose you have a sales data table for 2 stores as follows. The task is to calculate the total sales of store A in January and store B in February.
Sales data table - illustrative example of the SUMIFS functionInstructions:
Step 1: In cell D2, enter the formula as follows: =SUMIFS(C2:C7, A2:A7, 'A', B2:B7, 'January') + SUMIFS(C2:C7, A2:A7, 'B', B2:B7, 'February')
Step 2: Press Enter and view the result in cell D2.
How to use the SUMIFS function with date data
Essentially, using the SUMIFS function with date data is not much different from other types of data (text and numbers). Here's an illustrative example for you to follow the usage with this data type:
Problem: Suppose you have a table of product delivery quantities for various days as follows. The task is to calculate the total quantity of products delivered in the last 7 days.
Instructions:
Step 1: In cell F2, enter the formula as follows:
=SUMIFS(C2:C7,B2:B7,'>='&TODAY()-7)
Where:
- C2:C9: Is the range to sum (Quantity column).
- B2:B9: Is the range to evaluate the first condition (Delivery Date column).
- ''>='&TODAY()-7: Is the first condition, meaning only fetching data for the last 7 days (including today - TODAY()).
Step 2: Press Enter and view the result in cell F2.
In summary, this is a detailed guide on how to use the SUMIFS function to sum with 2 or more conditions in Excel. Through the instructions and illustrative examples, you can understand the practical usage of the function. Leave a comment below if you want to know more about this function and other Excel tricks.
- Explore more in the category: excel tips and tricks
