You can have the sum of the numbers based on the numbers and text in different columns using the SUMIFS function.
Example: You are working with a worksheet with the first name in column A, the last name in column B, the grade in column C, and the marks in column D.
Question: What is the sum of marks for students in Grade two with "son" in the last name?
=SUMIFS(D2:D10, C2:C10, "2", B2:B10, "*SON*")
The result returns 173.
Two students from grade two have "son" in their last names: row 3 and row 7. The wildcard asterisk (*) represents "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.