Sum if Begins With or Ends With – Excel & Google Sheets
Written by
Reviewed by
Download the example workbook
This tutorial will demonstrate how to use the SUMIFS Function to sum data corresponding to cells that begin or end with specific text in Excel and Google Sheets.
Sum if Text Starts With
First, we will demonstrate how to sum data rows with cells beginning with specific text using the SUMIFS Function.
The SUMIFS Function sums data rows that meet certain criteria. Its syntax is:
This example will sum all Scores with a State Name that starts with the text “New” using the SUMIFS Function and the * wildcard character.
=SUMIFS(C3:C9,B3:B9,"New*")
The * wildcard character can be used at the end of the search criteria to look for cells that start with the specified text. This sums the scores from the following states: New York, New Jersey, and New Mexico as they start with the text “New”.
Sum if Text Ends With
Similarly, we can sum all Scores for State Names that end with “o” by using:
=SUMIFS(C3:C9,B3:B9,"*o")
This sums the scores for New Mexico and Ohio.
Combining SUMIFS Wildcards with Cell References
Usually, it is not good practice to hard-code values into formulas. Instead, it’s more flexible to use separate cells to define the criteria.
To search for whether the cells starts with the text held in cell E3, we can use the SUMIFS Function with a cell reference and * wildcards:
=SUMIFS(C3:C9,B3:B9,E3&"*")
Note that the text “New” has been replaced by the cell reference E3 and the * character has been put into its own quotes (” “) and joined to the cell reference with the & symbol.
Sum if Begins With or Ends With in Google Sheets
These formulas work exactly the same in Google Sheets as in Excel.
