How SUMIFS Operates
The SUMIFS function is designed to calculate the sum of values contingent on multiple specified conditions. The function's syntax is outlined as follows:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Let's dissect each component of the function:
- sum_range: Signifying the range of cells earmarked for summation. For instance, designating column A for summation would entail entering A:A as the sum_range argument.
- criteria_range1: Identifying the initial set of cells acting as the first criterion for summation. If, for instance, you aim to sum cells in column A based on criteria in column B, inputting B:B for the criteria_range1 argument is appropriate.
- criteria1: Representing the criterion for the initial criterion range. If your objective is to sum cells in column A based on the criterion "greater than 10" in column B, the criteria1 argument would be >10.
- criteria_range2 (Optional): Providing the option to specify a second criterion range. For example, to sum cells in column A based on criteria "greater than 10" in column B and "less than 20" in column C, entering C:C for the criteria_range2 argument is necessary.
- criteria2: Designating the criterion for the second criterion range. In the aforementioned scenario, the criteria2 argument would be <20.
The SUMIFS function supports the specification of up to 127 criterion ranges and associated criteria.
Consider a basic example to grasp the functioning of the SUMIFS function. In the given scenario, the worksheet details sales made by distinct salespeople.
To accumulate the total sales for a specific salesperson, the SUMIFS function is applied as follows:
=SUMIFS(B:B, A:A, "John")
Executing this formula sums the values in column B based on the criterion "John" in column A.
Outcome: The computed result reveals the sum of all sales made by John. By adjusting the criterion, the same formula can be employed for other salespeople.
Now, delve into a more sophisticated example to understand how the SUMIFS function accommodates multiple criteria. In this case, the worksheet logs sales data categorized by salespeople and regions.
To calculate the total sales for each salesperson in each region, the SUMIFS function is executed as follows:
=SUMIFS(B:B, A:A, "John", C:C, "North")
This formula sums the values in column B based on the criteria "John" in column A and "North" in column C.
Outcome: The result showcases the sum of sales made by John in the North region. By altering criteria, the formula can be adapted to analyze sales for other salespeople and regions.
This comprehensive article has provided an in-depth exploration of leveraging the SUMIFS function in Excel. It elucidates the application of the function with a singular criterion and illustrates its utilization with multiple criteria. The guide also demonstrates the summation of values based on criteria in disparate ranges.
Should any inquiries arise concerning the SUMIFS function or any other Excel-related queries, feel free to engage in discussions within the AbleBits Community.