Excel SUMIF Equal to Cell: Formula & Examples

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF with a cell reference as the criteria by writing the formula as =SUMIF(range, A1, sum_range) — no quotes around the cell reference. For example, if A1 contains the text “North”, then =SUMIF(B2:B100, A1, C2:C100) sums all values in C2:C100 where the matching row in B2:B100 equals whatever is typed in A1.

Why Use a Cell Reference Instead of Typing the Value?

The core advantage of referencing a cell is that the formula updates automatically when you change the value in that cell. If you hard-code the criteria as =SUMIF(B2:B100, "North", C2:C100), you must edit the formula every time the criteria changes. With a cell reference, you can build a small dashboard where a dropdown in A1 controls which region’s sales are totaled — the formula stays untouched. This also makes the formula easier to read and audit, since the criteria is visible in a labeled cell rather than buried in the formula bar.

How SUMIF Works with Cell References

SUMIF takes three arguments: range (the cells to check against the criteria), criteria (what to look for), and sum_range (the cells to add). When the criteria is a cell reference, Excel evaluates the reference’s current value and uses it as the match condition. The matching is case-insensitive by default — “north” and “NORTH” are treated as equal. Text matches must be exact, including leading or trailing spaces, so if your data has inconsistent spacing, the sum may return 0.

For numeric criteria, the same logic applies. If D1 contains the number 100, then =SUMIF(A2:A50, D1, B2:B50) sums B values where A equals 100. You can also combine a cell reference with operators by using concatenation: =SUMIF(A2:A50, ">"&D1, B2:B50) sums values where A is greater than the number in D1. This works because the ampersand joins the comparison operator with the cell’s value into a single text string that SUMIF interprets as a condition.

Step-by-Step: Building a SUMIF with a Cell Reference

  1. Place the criteria value in a cell, for example, A1. Type the exact text or number you want to match.
  2. In the cell where you want the result, type =SUMIF(.
  3. Select the range to check, for example, B2:B100. Type a comma.
  4. Click on the cell containing the criteria (A1) or type its reference. Do not add quotes around it.
  5. Type a comma, then select the sum range, for example, C2:C100.
  6. Close the parenthesis and press Enter. The result appears immediately.

If your data is in a table (Insert > Table), you can use structured references like =SUMIF(Table1[Region], A1, Table1[Sales]), which automatically adjusts as you add rows. On Mac, the steps are identical; the formula bar and function names are the same. In Excel for the web, SUMIF works exactly as in desktop Excel, but you cannot use array constants or some legacy functions — none of which apply here.

Common Problems and How to Fix Them

  • Result is 0: Check for extra spaces in the criteria cell or in the data range. Use =TRIM(A1) in a helper cell to clean the criteria, or clean the data with =TRIM(B2).
  • Text vs. number mismatch: If the criteria cell contains a number formatted as text (e.g., with an apostrophe), SUMIF won’t match numeric cells. Use =VALUE(A1) or re-enter the number.
  • Case sensitivity: SUMIF is case-insensitive. If you need exact case matching, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(B2:B100,A1))*C2:C100).
  • Wildcards in the criteria cell: If A1 contains an asterisk (*) or question mark (?), SUMIF treats them as wildcards. To match a literal asterisk, use =SUBSTITUTE(A1,"*","~*") in a helper cell.
  • Whole-column references: Using =SUMIF(B:B, A1, C:C) works but may slow down large workbooks. Prefer a defined range like B2:B1000.

SUMIF vs. SUMIFS vs. SUMPRODUCT

SUMIF handles a single condition. If you need multiple criteria, use SUMIFS, which requires the sum range first: =SUMIFS(C2:C100, B2:B100, A1, D2:D100, ">100"). SUMIFS also supports cell references in the same way — just place the reference where the criteria goes, no quotes. SUMPRODUCT is more flexible for array operations, such as summing with case-sensitive matches or using OR logic, but it is slower on large datasets. For most single-condition tasks, SUMIF with a cell reference is the simplest and fastest choice.

Real-World Example with Numbers

Assume you have a sales log: column A lists product names (A2:A20), column B lists units sold (B2:B20). You want to total units for a product whose name you type into cell D1. In E1, enter =SUMIF(A2:A20, D1, B2:B20). If D1 contains “Widget” and the data has Widget rows with units 5, 3, and 7, the formula returns 15. If you change D1 to “Gadget” and the Gadget rows have units 2 and 4, the formula returns 6. No formula editing is needed — just update D1.

Frequently Asked Questions

Can I use a cell reference with a comparison operator like “>”? Yes, but you must concatenate: =SUMIF(A2:A20, ">"&D1, B2:B20). The ampersand joins the operator with the cell’s value into a text string that SUMIF interprets correctly.

Does SUMIF work with dates in a cell? Yes. If D1 contains a date, =SUMIF(A2:A20, D1, B2:B20) matches dates exactly. For date ranges, use =SUMIFS(B2:B20, A2:A20, ">="&D1, A2:A20, "<="&D2).

What if my criteria cell is blank? SUMIF treats a blank cell as an empty string "", so it will sum rows where the range cell is also blank. To avoid this, wrap the formula in an IF: =IF(D1="","",SUMIF(A2:A20,D1,B2:B20)).

Can I use a cell reference for the range or sum_range? Yes, you can use INDIRECT to reference ranges dynamically: =SUMIF(INDIRECT(D2), D1, INDIRECT(D3)) where D2 contains "A2:A20" and D3 contains "B2:B20". This is useful for dropdown-driven range selection.

Why does my formula return #VALUE! error? This usually happens when the criteria cell contains a formula error or when the range and sum_range are different sizes. Ensure both ranges have the same number of rows and columns.

Is there a limit to how many rows SUMIF can handle? No hard limit, but performance degrades with hundreds of thousands of rows. For very large datasets, consider using a PivotTable or Power Query instead.

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.