I. Intro
The SUMIFS function returns the sum of values that meet multiple conditions.
II. About the function
- Formula: =SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,...],[criteria2,...])
- Arguments:
- Sum range: The range to be summed.
- Criteria range 1: The area used to query "Criteria 1".
- Criteria 1: Pairs with "Criteria range 1". The function searches for cells in 'Criteria range 1' that meet this argument. If cells that meet the criteria are found, the corresponding cells in "Sum range" will be summed.
- Criteria range 2: Optional. The area used to query "Criteria 2".
- Criteria 2: Optional. Pairs with "Criteria range 2"
Note: Up to 127 pairs of criteria and criteria range can be added.
- Example: =SUMIFS(A1:A10, B1:B10, "greater than 20", C1:C10 ,"less than 30") calculates the sum of all numbers in the A1:A10 range, where values in column B is greater than 20 and the values in column C is less than 30.
III. Steps
Use the function
- Open the spreadsheet. Select a cell and enter =SUMIFS(sum range, criteria range 1, criteria 1, [criteria range 2, ...], [criteria 2, ...]).
- Press Enter to get the result.
For example, =SUMIFS(D2:D12,B2:B12,"greater than 15",C2:C12,"greater than 25") in the image below calculates the sum of cells that meet both criteria, which are cells D5, D6, and D9.
250px|700px|reset
Delete the function
Select the cell with the SUMIFS function, and press Delete.
IV. Use cases
Find the sum of values that meets the specified criteria
For data or statistical analysis, the SUMIFS function can help you find the sum for only values that meet the criteria you set.
- Formulas used below: =SUMIFS(E2:E9,B2:B9,">100",C2:C9,">90")
- About the arguments: Identifies the number of new visitors to a webpage with a PV (page view) of over 100 and UV (unique visitor) of over 90.
- E2:E9 lists new visitors, which is the range that contains the value you want to sum.
- B2:B9 contains PV, which is the first set of data to be evaluated against the first criteria.
- ">100" is the first criteria, which is PV > 100.
- C2:C9 contains UV, which is the second set of data to be evaluated against the second criteria.
- ">90" is the second criteria, which is UV > 90.
250px|700px|reset