Sum if Begins With or Ends With – Excel & Google Sheets

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 begins ends with Main Function

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:

SUMIFS syntax

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 Text Start (HardCode)

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 Text End HardCode

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-Text Start CellRef

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.

sum-if-begins-ends-with-google-Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!