Excel SUMIF Contains Number? Match Digits or Values

Formulas & functions · Updated November 19, 2026

Yes—Excel’s SUMIF can sum rows where a text entry contains particular digits by using asterisks as wildcards, such as "*23*". If you mean a numeric value that meets a condition, such as being at least 23, use a numeric comparison instead; a number does not have a substring until Excel treats it as text.

How SUMIF evaluates a condition

SUMIF checks each cell in a criteria range, decides whether it meets the criterion, and adds the corresponding value from a sum range. Its syntax is SUMIF(range, criteria, [sum_range]). The sum range is optional: if you omit it, Excel adds the matching cells in the criteria range itself.

For example, with product names in A2:A10 and sales amounts in B2:B10, the range to check is A2:A10 and the values to add are in B2:B10. The criterion determines what counts as a match. It can be a number, text, a comparison such as “greater than 100,” a wildcard pattern, or a reference to a cell containing a criterion.

The important distinction is between a number’s value and digits appearing in a text string. A cell containing the number 1234 is a numeric value. A cell containing the text “Order 1234” is text that includes those digits. SUMIF can compare the first as a number, and use wildcards to find the digits in the second.

Use wildcards to find digits inside text

In a SUMIF criterion, an asterisk (*) represents any number of characters, including no characters. Put one on each side of the digits to match text that contains those digits anywhere. For instance, "*23*" can match “INV-23,” “1234,” or “item 230.” SUMIF’s text matching is not case-sensitive, though that does not affect digit searches.

With order codes in A2:A10 and amounts to add in B2:B10, this formula adds amounts for rows whose order code contains the text 23:

=SUMIF(A2:A10,"*23*",B2:B10)

A question mark (?) is a different wildcard: it matches exactly one character. It is useful when the position and length of a pattern matter, but an asterisk is generally the right choice for “contains.” If you need to search for a literal asterisk or question mark rather than use it as a wildcard, put a tilde (~) before it in the criterion.

Worked example with order codes

Suppose A2:A6 contains the text codes “INV-123,” “ORD-123-X,” “INV-124,” “8123,” and “INV-223.” B2:B6 contains 40, 25, 30, 15, and 12, respectively. To add amounts for codes containing the digits 123, use this formula; it returns 80 because it adds 40 and 25, but not 15 or 12:

With order codes in A2:A6 and amounts in B2:B6, use:

=SUMIF(A2:A6,"*123*",B2:B6)

The code “8123” does not match “*123*” because its last three digits are 123, so it does match; therefore the total is actually 40 + 25 + 15 = 80. “INV-223” does not match. This illustrates why it helps to inspect the matching rows: a contains search finds the digits wherever they occur, not just at the start or as a complete code.

To require the code to start with 123, put the asterisk only after the digits: "123*". To require it to end with 123, put the asterisk before them: "*123". These patterns can prevent unintended matches when codes have a known structure.

Use a cell reference for a changeable search

You do not have to type the digits into the formula. Put the search text in a separate cell, such as D1, so someone can change the search without editing the formula. With order codes in A2:A10, amounts in B2:B10, and the digits to find in D1, use:

=SUMIF(A2:A10,"*"&D1&"*",B2:B10)

The ampersands join the wildcard characters to the contents of D1. If D1 contains 23, the resulting criterion is equivalent to "*23*". Enter the search value as text if you need to preserve leading zeros, such as “007”; a numeric cell holding 7 does not retain those zeros as part of its value.

When the criterion is a numeric value

If the question is whether a number equals, exceeds, or falls below a threshold, use a numeric criterion rather than a wildcard. For example, with unit counts in A2:A10 and sales amounts in B2:B10, this adds the amounts for rows with at least 100 units:

=SUMIF(A2:A10,">=100",B2:B10)

Comparison operators used in criteria include >, <, >=, <=, and <> for “not equal to.” To take the threshold from D1, with the same layout, use:

=SUMIF(A2:A10,">="&D1,B2:B10)

This tests the numeric value against the threshold. It does not search the number’s written digits: a value of 1234 is greater than or equal to 23, but that is a numeric comparison, not a “contains 23” test. Also check whether imported values that look like numbers are actually stored as text; inconsistent types can make comparisons and matches behave differently than expected.

What if the cells contain a mixture of numbers and text?

A wildcard criterion such as "*23*" is intended to match text strings. It will not reliably find a number stored as a numeric value by treating its digits as a text substring. That matters in a column where some entries are text codes and others are actual numbers. If your intended rule is “convert each entry to text, then look for these digits,” use a formula that explicitly does that conversion.

With mixed entries in A2:A10, numeric amounts in B2:B10, and the search text 23, this formula converts the checked entries to text for the search and adds amounts for matches:

=SUMPRODUCT(--ISNUMBER(SEARCH("23",A2:A10&"")),B2:B10)

SEARCH returns a position when it finds the digits and an error when it does not. ISNUMBER turns those results into TRUE or FALSE, and the double minus converts them to 1s and 0s so SUMPRODUCT can include only matching amounts. The empty string joined to each cell coerces numeric entries to text. The formula searches the underlying number, not any special display formatting. The checked range and amount range should be the same size, and the amount range should contain numbers.

This approach answers a different question from a numeric test. If you need both kinds of logic—for example, entries containing 23 and amounts greater than 50—spell out the combined conditions rather than assuming “contains” has a numeric meaning.

Common problems and checks

  • The wildcard formula returns zero. Check whether the entries are numeric rather than text, whether the search digits are present, and whether the formula’s ranges cover the intended rows. For mixed types, consider the SUMPRODUCT approach above.
  • It matches more rows than expected. “Contains” matches digits anywhere. For a prefix or suffix, use "123*" or "*123"; for an exact text match, use the text itself without wildcards.
  • Leading zeros disappear. Excel stores numeric values separately from their displayed formatting. If a code such as 007 is an identifier, store it as text, or deliberately convert numbers using the required fixed-width format before searching.
  • Totals look wrong after importing data. Some entries may be text numbers while others are numeric, and some sum cells may themselves be stored as text. Normalize the source data where possible and verify a few matching rows and their amounts.
  • Ranges do not line up. The criteria range and sum range should cover corresponding rows and have compatible dimensions; otherwise, the formula may add values from unintended positions.

For a one-off check, filtering or searching the source data can help verify which rows should qualify. For a repeatable calculation, keep the criterion and data ranges clearly separated and use a table or appropriately sized ranges so newly added records are not accidentally left out.

SUMIF or another approach?

Use SUMIF when one condition determines which corresponding values to add. It is straightforward for a wildcard search over text or a numeric threshold. If you need multiple conditions, SUMIFS is designed to sum a range using several criteria. If the condition must search mixed numeric and text values as converted text, the SUMPRODUCT formula is a practical alternative because it can evaluate the conversion and search for each row.

Choose based on what the data means, not merely how it looks on screen. Product codes, postal codes, and account identifiers are usually labels, even if they contain only digits; treating them as text preserves their identity and any leading zeros. Counts, prices, and quantities are usually numeric values and should be tested with comparisons or exact numeric criteria.

Frequently Asked Questions

Can SUMIF find a number anywhere in a cell?
Yes, if the cell contains text: use a criterion such as "*45*" to find text containing 45. For numeric cells, a wildcard does not mean “search the digits”; convert the entries to text as part of the calculation or use another data-preparation method.

Does SUMIF with *123* match the number 123?
Not as a substring search when the cell contains numeric 123. The wildcard criterion is for text matching. If you want cells numerically equal to 123, use 123 as the criterion; if you want any numeric value whose written digits include 123, explicitly convert values to text and search them.

Is SUMIF case-sensitive?
No. SUMIF text criteria are not case-sensitive. This makes no difference for digits, but it matters if you are searching text and need uppercase and lowercase to count as different.

Can I use a cell reference for the digits to search?
Yes. Join a reference to wildcard characters, as in "*"&D1&"*". Put the digits in D1 and use that joined criterion in SUMIF. Keep leading zeros as text if they are significant.

Why does SUMIF find a displayed number differently than expected?
A cell’s displayed format is not necessarily its stored value. A number formatted to show leading zeros still has its underlying numeric value, while a code entered as text retains its characters. Check the actual cell contents and choose a text search or numeric condition to match the question you mean to ask.

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.