Excel SUMIF Starts With Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF to sum values where a corresponding text cell starts with specific characters, by using a wildcard asterisk (*) in the criteria. For example, =SUMIF(A2:A10,"App*",B2:B10) sums all amounts in B2:B10 where the text in A2:A10 begins with “App”. This works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions, as well as Excel for the web and Mac.

How SUMIF Handles Wildcards

SUMIF (and its sibling COUNTIF) support three wildcard characters that let you match partial text without needing complex formulas:

  • Asterisk (*) – matches any sequence of characters (including none). Putting it at the end, like "App*", matches any text that starts with “App”.
  • Question mark (?) – matches exactly one character. Use "A?p" to match “App”, “Amp”, “Aop”, etc.
  • Tilde (~) – escapes the wildcard, so "~*" matches a literal asterisk character.

These wildcards work in the criteria argument of SUMIF, SUMIFS, COUNTIF, and COUNTIFS. They do not work in functions like VLOOKUP or XLOOKUP unless you use a different approach.

Basic Formula Examples

Assume you have a product list in column A (A2:A10) and sales amounts in column B (B2:B10). Here are concrete examples:

  • Sum sales for products starting with “Pro”: =SUMIF(A2:A10,"Pro*",B2:B10)
  • Sum sales for products starting with “A” (case-insensitive): =SUMIF(A2:A10,"A*",B2:B10)
  • Sum sales for products that start with any single character then “bc”: =SUMIF(A2:A10,"?bc*",B2:B10)

If your range contains “Apple”, “Application”, and “Banana” in A2:A4, and B2:B4 has 10, 20, 30, then =SUMIF(A2:A4,"App*",B2:B4) returns 30 (10+20).

Using SUMIFS for Multiple Conditions

SUMIFS allows multiple criteria. To sum amounts where the text starts with “Pro” and the date in column C is after a certain date, use:

With A2:A10 as text, B2:B10 as amounts, and C2:C10 as dates, enter: =SUMIFS(B2:B10,A2:A10,"Pro*",C2:C10,">"&DATE(2024,1,1))

This sums all amounts where the product starts with “Pro” and the date is after January 1, 2024. Note the ampersand (&) to combine the comparison operator with the date function.

Case Sensitivity and Exact Matches

SUMIF and SUMIFS are not case-sensitive by default. =SUMIF(A2:A10,"app*",B2:B10) will match “Apple”, “APPLE”, and “appetizer”. If you need case-sensitive matching, you must use SUMPRODUCT with the EXACT function. For example, to sum only where the text starts with lowercase “app”:

With A2:A10 and B2:B10, use: =SUMPRODUCT((LEFT(A2:A10,3)="app")*B2:B10) – but this is case-sensitive because the comparison is literal. However, this formula will match any text whose first three characters are exactly “app”, regardless of what follows. To make it truly case-sensitive and only match lowercase “app” at the start, use: =SUMPRODUCT((EXACT(LEFT(A2:A10,3),"app"))*B2:B10).

Common Problems and Fixes

Three frequent issues arise when using SUMIF with starts-with text:

  • Wildcard not working – If your criteria is a cell reference, like =SUMIF(A2:A10,C1&"*",B2:B10), you must add the asterisk with an ampersand. A plain cell reference like C1 will look for an exact match.
  • Text with leading spaces – If cells contain spaces before the text, “App*” won’t match. Use TRIM or clean the data first.
  • Numbers stored as text – SUMIF sums numbers, but if your values are text-formatted numbers, they may be ignored. Convert them to numbers using VALUE or Text to Columns.

Alternatives: SUMPRODUCT and FILTER

For more flexibility, SUMPRODUCT can handle starts-with logic without wildcards. To sum B2:B10 where A2:A10 starts with “Pro”: =SUMPRODUCT((LEFT(A2:A10,3)="Pro")*B2:B10). This is case-sensitive and does not use wildcards. If you have Microsoft 365 or Excel 2021, you can also use FILTER: =SUM(FILTER(B2:B10,LEFT(A2:A10,3)="Pro")). Both are useful when you need to combine with other functions or perform case-sensitive matching.

Frequently Asked Questions

Does SUMIF work with partial text in the middle of a cell? Yes, but you need to put asterisks on both sides: "*text*" matches any cell containing “text” anywhere. To match text that starts with a value, put the asterisk only at the end.

How do I sum if a cell starts with a number? Use a wildcard with a number, but be careful: =SUMIF(A2:A10,"1*",B2:B10) will sum where the text starts with the character “1”. If the cell contains a numeric value 1, it won’t match because SUMIF treats numbers differently. For numeric values, use criteria like ">=1" and "<2" instead.

Can I use a cell reference for the start text in SUMIF? Yes, concatenate the cell with the wildcard: =SUMIF(A2:A10,D1&"*",B2:B10) where D1 contains the starting text. This is the standard way to make the criteria dynamic.

Why does my SUMIF return 0 even though there are matches? Check for invisible characters, extra spaces, or non-printable characters in the data. Use the LEN function to compare the length of the cell with the visible text. Also ensure the criteria range and sum range are the same size.

Is there a way to sum based on starts-with in Google Sheets? Yes, Google Sheets supports the same wildcard syntax: =SUMIF(A2:A10,"App*",B2:B10) works identically. The main difference is that Google Sheets is case-insensitive as well, and the FILTER function is available in both.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.