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 likeC1will 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
- Excel Power Query Prevent Refresh: How to Stop It
- Excel Power Query Slow to Refresh? Fixes That Work
- Excel Power Query: Merge Rows from Columns or Tables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Date in Excel: Count Dates, Days, and RangesTo count cells that contain a specific date in Excel, use COUNTIF with a date value as its…
- SPLIT in Snowflake SQL: Functions, Examples, and TipsIn Snowflake SQL, SPLIT(string, delimiter) divides a string wherever a literal delimiter occurs and returns the pieces as…
- Excel Formula to Check Whether a Value Is in a ListWith list items in A2:A10 and the value to check in D2, use =IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"). COUNTIF checks whether…
- Java String Functions: Methods, Examples, and GuideOn W3Schools, “Java string functions” refers mainly to methods of Java’s String class, such as length(), toUpperCase(), indexOf(),…
- Excel Cell Fill Shortcut: Fill Down, Fill Right, and ColorIn Excel for Microsoft 365 on Windows, press Ctrl+D to fill down from the top cell of a…
- How to Paste in Excel Without Formulas and Keep ValuesTo paste in Excel without copying formulas, copy the cells, select the destination, then choose Home > Paste…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.