The SUMIFS function sums the cells that meet one or more criteria, and you can use the function to sum cells with the corresponding cells that are text strings in other columns.
Example: You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.
Question: What is the sum of marks for students' first names starting with an E and "son" in the last name?
=SUMIFS(D26:D34, A26:A34, "E*", B26:B34, "*SON*")
The result returns 174.
Two students have E in their first names and "son" in their last names: row 3 and row 6. The wildcard asterisk (*) represents "all letters" before or after the text "son".
Notes: The SUMIFS Function
The SUMIF function sums the values in a range that meets the specified criteria.
Formula:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Explanations:
- – Sum_range is required, the range to sum.
- – Criteria_range1 is required, the range for the first criteria to meet.
- – Criteria 1 is required, the first criterion to meet.
- – Criteria_range2 is optional, the range for the second criteria to meet.
- – Criteria 2 is optional, the second criterion to meet.