SUMIF Excel Cell Value: Criteria, Examples & Fixes

To make SUMIF use a value stored in a cell, put the cell reference in its criteria argument. With regions in A2:A7, sales in B2:B7, and the region to find in D2, enter =SUMIF(A2:A7,D2,B2:B7); if D2 contains East and the East sales are 120, 80, and 100, the result is 300.

What SUMIF does

SUMIF adds numbers that meet one condition. That condition can be a literal value typed into a formula or a value held in a worksheet cell. Using a cell makes the calculation easier to reuse: change the criterion cell and the total updates, without rewriting the formula.

The function has three arguments, in this order: the cells Excel checks, the condition it checks for, and the cells it adds when the condition is met. The third argument is optional. If you leave it out, Excel checks and adds values in the first range. The criterion can be a text value, number, expression, wildcard pattern, or reference to a cell containing one of those values.

For the opening example, the rows can be: East / 120, West / 90, East / 80, North / 75, East / 100, and West / 60, with region in column A and sales in column B. D2 contains East. SUMIF finds the three matching region labels and adds their corresponding sales: 120 + 80 + 100 = 300. The criterion cell itself does not need to be beside the data.

How the cell reference works

When the criterion is a plain cell reference, Excel reads the value in that cell and looks for a match. Do not put quotation marks around the reference: a reference such as D2 means “use what is in D2,” while a quoted string would be treated as literal text. The range being checked and the sum range should cover the corresponding rows, so each match is paired with the intended amount.

With product names in A2:A5, amounts in B2:B5, and the requested product in D2, this formula returns the sum for exact matching names. If the rows are Tea / 12, Coffee / 25, Tea / 8, Coffee / 15, and D2 contains Coffee, the result is 40.

=SUMIF(A2:A5,D2,B2:B5)

For a numeric criterion, the same idea applies. With amounts in B2:B7 and the target number 90 in E2, this formula sums values equal to 90; given the opening sales figures, the result is 90.

=SUMIF(B2:B7,E2)

Because the sum range is omitted in that example, Excel checks and adds the same range. That can be useful for adding numbers that meet a numeric condition, but if one column contains labels and another contains amounts, specify the sum range explicitly.

Using comparison operators stored with a cell value

A common point of confusion is how to compare values against a number stored in a cell. Typing an operator into the criterion requires combining the operator text with the cell reference. The ampersand joins them into one criterion; it does not add the cells’ numeric values.

With sales in B2:B7 and a minimum amount in E2, use this formula to add values greater than or equal to the threshold. If E2 contains 100 and the values are 120, 90, 80, 75, 100, and 60, the result is 220, because 120 and 100 qualify.

=SUMIF(B2:B7,">="&E2)

The operator is inside quotation marks, and the ampersand connects it to E2. The same pattern works with operators such as greater than, less than, not equal to, and less than or equal to. For example, changing the operator to less than or equal to would include the threshold itself; choosing less than would exclude it. When you put the operator and number together in one cell, SUMIF can also use that cell directly as its criterion.

Text criteria, partial matches, and wildcards

For an exact text match, put the desired text in a cell and refer to that cell. Excel’s text matching in SUMIF is generally not case-sensitive, so East and EAST match the same criterion. Leading or trailing spaces are still characters, however, and can prevent an apparent match.

To match text that contains a word rather than equals it, use asterisks as wildcards. An asterisk represents any number of characters, while a question mark represents one character. A tilde lets you search for a literal wildcard character instead of treating it as a pattern symbol.

With descriptions in A2:A5, amounts in B2:B5, and the search word in D2, this formula matches any description containing the text in D2. If the rows are Ground coffee / 25, Tea bags / 8, Coffee beans / 18, and Mug / 12, and D2 contains Coffee, the result is 43.

=SUMIF(A2:A5,"*"&D2&"*",B2:B5)

Here the asterisks are on both sides of the cell value, so the word can appear at the start, middle, or end of a description. If D2 is empty, that pattern can match every text entry, which may produce an unexpectedly broad total. Check that a search cell is populated when using a wildcard formula.

Working with dates in a criterion cell

Excel stores real dates as serial numbers, even though cells display them in a calendar format. A date comparison works reliably when the criterion cell contains an actual Excel date rather than text that merely looks like one. You can test for dates on or after a chosen date by joining the comparison operator to the date cell.

With dates in A2:A4, amounts in B2:B4, and a valid start date in F2, this formula adds amounts on or after F2. If the rows are January 1, 2026 / 40; January 15, 2026 / 60; and February 1, 2026 / 90, and F2 is January 15, 2026, the result is 150.

=SUMIF(A2:A4,">="&F2,B2:B4)

For a date interval, SUMIFS is usually clearer because it supports multiple conditions. With the same rows and ranges, F2 set to January 1, 2026, and F3 set to February 1, 2026, this formula includes the start date but excludes February 1 and later. The result is 100: 40 + 60.

=SUMIFS(B2:B4,A2:A4,">="&F2,A2:A4,"<"&F3)

The exclusive upper bound is useful when dates may include times as well as days. If a date cell contains a time, comparing against the next day with a less-than condition includes all times on the intended final day without needing to guess the last possible time value.

Choosing SUMIF or SUMIFS

Use SUMIF when one condition determines which values to add, such as sales for the region named in D2. Use SUMIFS when every row must meet two or more conditions, such as a region and a date range. In SUMIFS, the sum range comes first, followed by pairs of criteria ranges and criteria; that argument order differs from SUMIF.

For example, a monthly report might need to total a selected region only during a selected period. Adding conditions separately is more dependable than combining fields into improvised text or making several overlapping SUMIF calculations. If the condition is only one cell value, however, SUMIF is shorter and easier to audit.

SUMIF is also different from COUNTIF: SUMIF adds matching numeric values, whereas COUNTIF counts matching cells. If you need to count rows that meet conditions, use COUNTIF for one condition or COUNTIFS for multiple conditions. If you need to return matching records rather than aggregate them, a filtering function may be a better fit in a version that supports it.

Common errors and ways to troubleshoot

Wrong cell reference: Confirm the criterion cell is the one holding the value you want. A formula can be syntactically valid while pointing to a blank or unrelated cell.
Text stored differently: Look for extra spaces, inconsistent spelling, or numbers stored as text. These can make values that look alike behave differently.
Operator entered as literal text: For a threshold held in a cell, join the quoted operator to the reference. Do not expect a criterion cell containing only a number to imply “greater than.”
Wrong sum range: Check that the sum range lines up row by row with the range being tested. Matching a label in one row should add the amount from that same row.
Dates are text: If dates do not compare as expected, verify that Excel recognizes them as dates. Changing a cell’s number format alone does not convert text into a real date.
Unexpected zero: Check that at least one criterion value matches and that the cells to be added contain numbers. Also inspect hidden spaces and formula references.
Formula separator differs: The examples use commas, as in standard English-language Excel settings. Some regional settings require semicolons between arguments; use the separator Excel expects on that computer.

To inspect a result, select a matching row and compare its criterion value with the criterion cell, then verify the corresponding amount is numeric. For formulas copied down a report, consider whether the criterion reference should move with each row or stay fixed. Relative references move when copied; an absolute reference, written with dollar signs around its column and row, stays fixed. Use Excel’s F4 key while editing a reference in Windows to cycle through reference styles; on Mac, the equivalent shortcut can depend on the keyboard and Excel settings.

Frequently Asked Questions

How do I make SUMIF use a value in another cell?

Put the desired criterion in a cell and use that cell reference as SUMIF’s second argument. For a text match, a plain reference is sufficient; for a comparison such as “at least this number,” combine the quoted operator with the reference.

Do I put quotation marks around the cell reference in SUMIF?

No. A bare cell reference tells Excel to read the value in that cell. Quotation marks are used for literal text or comparison operators, not around a reference that should update when its cell changes.

How can I use greater than with a cell value?

Join a quoted greater-than operator to the cell reference with an ampersand. This creates a comparison criterion using the number currently in that cell; use greater than or equal to if the threshold itself should qualify.

Can SUMIF match part of a cell’s text?

Yes. Use asterisks around the text criterion to match it anywhere within a text value. A question mark matches one character, and a tilde can escape a wildcard when you need to search for the actual symbol.

Why does SUMIF return zero when the value looks like a match?

Check for spaces, spelling differences, a criterion reference pointing to the wrong cell, text-formatted numbers, or dates stored as text. Also verify that the sum range contains numeric values in the rows that match.

Can SUMIF use more than one cell condition?

SUMIF handles one condition. For multiple simultaneous conditions, use SUMIFS, which takes a sum range followed by criteria-range and criterion pairs. That is useful for reports filtered by factors such as both region and date.

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.