How to Sum Cells based on Text and Numbers

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.


=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)


  • - 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.

Leave a Reply