Buzz
You may know how to calculate sums in Excel using the SUM() function, but calculating sums of cells meeting multiple conditions might be unfamiliar. Enter SUMIFS(), which swiftly calculates sums of cells within a range satisfying one or more conditions.
This article below will describe the syntax, functionality, and usage of the SUMIFS() function in Excel.
Description
SUMIFS() function is used to calculate the sum of cells within a selected range that meet one or more conditions.
Syntax
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)
Where:
- sum_range: refers to the cells to be summed, including numbers or names, ranges or references containing numbers, empty cells, and text values are ignored. This parameter is required.
- criteria_range1: specifies the range to be evaluated based on the condition, mandatory.
- criteria1: represents the condition in the form of a number, expression, cell reference, mandatory.
- criteria_range2,criteria2,… : optional additional ranges and conditions, allowing up to 127 pairs of criteria_range,criteria.
Note
- Each cell within the sum_range will be added together when all corresponding conditions defined for that cell are met.
- Cells within sum_range containing TRUE are considered as 1, cells containing FALSE are considered as 0.
- Each criteria_range must have the same size and shape as sum_range, meaning criteria_range and sum_range must have the same number of rows and columns.
- In criteria conditions, you can use wildcard characters: question mark (?) to replace any single character, asterisk (*) to replace any string. If the condition includes a question mark or asterisk, you must enter a tilde (~) before it.
Example
For the following data table:
Requirement:
1. Calculate the total quantity of products sold by the employee named Nga with STT < 5.
2. Calculate the total quantity of products sold by the employee named Hương excluding all products except 'Na'.
After applying the SUMIFS() function, the results are as follows:
Thus, with the content presented in this article, you now have the syntax, functionality, and usage of the SUMIFS() function demonstrated through specific examples. We hope this article is helpful to you. Wishing you success!
