Excel SUMIF Equal to Cell Value: How To

Formulas & functions · Updated November 22, 2026

Yes, you can use SUMIF with a cell reference as the criteria by simply typing the cell reference (like D1) directly into the criteria argument, and Excel will sum all values in the sum range where the corresponding cell in the criteria range equals the value in that cell. For example, =SUMIF(A2:A10, D1, B2:B10) sums all values in B2:B10 where the matching cell in A2:A10 equals whatever is typed in D1.

What SUMIF Does and When to Use It

SUMIF is one of Excel’s oldest and most used conditional functions. It adds up numbers in a range only when a related cell meets a condition. The syntax is:

=SUMIF(range, criteria, [sum_range])

  • range – the cells you want to check against the condition (e.g., a column of product names).
  • criteria – the condition that determines which cells to include. It can be a number, text, expression (like “>10”), or a cell reference.
  • sum_range – the actual numbers to add. If omitted, Excel sums the range itself.

You use SUMIF when you need a quick total based on one condition, such as total sales for a specific product, total hours for a specific employee, or total expenses in a certain category. For multiple conditions, you would use SUMIFS instead.

Using a Cell Reference as the Criteria

The most straightforward way to make SUMIF equal to a cell value is to put the cell reference in the criteria argument. For instance, if you have a list of salespeople in column A (A2:A20) and their sales in column B (B2:B20), and you want to total sales for the person whose name is in cell D1, you write:

=SUMIF(A2:A20, D1, B2:B20)

This works because Excel interprets the reference as “the value contained in D1”. If D1 contains “Smith”, the formula sums all B values where A equals “Smith”.

You can also use cell references with logical operators by concatenating. For example, to sum all values greater than the number in cell E1, you write:

=SUMIF(B2:B20, ">"&E1)

The ampersand (&) joins the operator with the cell value. Without it, Excel would treat “>E1” as literal text and return zero.

Step-by-Step Example with a Real Data Set

Let’s walk through a concrete example. Suppose you have a small table:

  • A1: “Product”, B1: “Sales”
  • A2: “Apples”, B2: 100
  • A3: “Bananas”, B3: 150
  • A4: “Apples”, B4: 200
  • A5: “Cherries”, B5: 75

You want the total sales for “Apples”. Type “Apples” in cell D1. Then in cell E1, enter:

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

Excel checks each cell in A2:A5. It finds “Apples” in A2 and A4, so it adds B2 (100) and B4 (200) to return 300. If you change D1 to “Bananas”, the formula recalculates to 150.

This is far more flexible than typing the criteria directly, because you can change the cell and the formula updates instantly.

Common Problems and How to Fix Them

Several issues trip up users when using a cell reference in SUMIF:

  • Text vs. number mismatch: If your criteria cell contains a number formatted as text (e.g., with an apostrophe or from a text import), SUMIF won’t match numeric cells. Check the cell format and use the VALUE function if needed.
  • Leading/trailing spaces: A space in the criteria cell (e.g., “Apples “) will cause no matches. Use TRIM() on the criteria or the range, or clean the data.
  • Case sensitivity: SUMIF is not case-sensitive by default, so “apples” matches “APPLES”. If you need case-sensitive matching, use SUMPRODUCT with EXACT.
  • Wildcards: If your criteria cell contains an asterisk (*) or question mark (?), Excel treats them as wildcards. To match a literal asterisk, use a tilde (~) before it, like "~*".
  • Entire column references: Using full columns like A:A works but may slow down large workbooks. Prefer a defined range.

SUMIF vs. SUMIFS vs. SUMPRODUCT

SUMIF handles a single condition. If you need to match a cell value and another condition, use SUMIFS. For example, to sum sales for “Apples” in the West region (with region in column C):

=SUMIFS(B2:B20, A2:A20, D1, C2:C20, "West")

SUMIFS has a different argument order: sum_range comes first.

SUMPRODUCT is a more powerful alternative that can handle arrays and case-sensitive matches. For the same single condition, you could write:

=SUMPRODUCT((A2:A20=D1)*B2:B20)

This multiplies each B value by 1 if the condition is true, then sums. It’s useful when you need to perform calculations inside the condition, but it’s generally slower than SUMIF on large ranges.

Dynamic Arrays and Modern Alternatives

If you have Microsoft 365 or Excel 2021, you can use the FILTER function to get a dynamic array of matching values and then sum them:

=SUM(FILTER(B2:B20, A2:A20=D1))

This returns a spilled array of all matching sales, and SUM adds them. It’s more flexible because you can further process the array, but it requires the newer Excel versions. SUMIF remains the simplest and most compatible choice for a basic conditional sum.

Frequently Asked Questions

Can I use a cell reference with a wildcard in SUMIF?
Yes. If you put “App*” in cell D1, SUMIF will treat it as a wildcard and match any text starting with “App”. To match a literal asterisk, use “~*” in the cell.

Why does my SUMIF return 0 when I reference a cell?
Check that the criteria cell contains exactly the same value as the range cells, including no extra spaces. Also verify that the sum range is numeric and not stored as text. Use the ISNUMBER function to test.

Does SUMIF work with dates in a cell reference?
Yes, but you need to be careful with date formatting. If your criteria cell contains a date, SUMIF will match dates in the range based on their serial number. Ensure both are real dates, not text.

Can I use a cell reference for the sum_range?
Yes, the sum_range can be a cell reference or a named range. For example, =SUMIF(A2:A10, D1, B2:B10) works fine.

Is there a way to sum if a cell is not equal to a value?
Yes, use the not-equal operator: =SUMIF(A2:A10, "<>"&D1, B2:B10). This sums all values where the corresponding cell is not equal to D1.

Using SUMIF with Cell References in Practice

In real workbooks, you’ll often set up a criteria cell that users can change. For example, a dashboard might have a dropdown in D1 to select a month, and a formula like =SUMIF(MonthRange, D1, SalesRange) updates the total instantly. This pattern is clean and avoids editing formulas repeatedly.

Remember that SUMIF is not case-sensitive and ignores blank cells in the criteria range. If your data has blank cells, they won’t match anything unless your criteria is an empty string.

For large datasets, consider using a PivotTable instead of many SUMIF formulas, as it’s more efficient and flexible. But for a quick single-condition total, SUMIF with a cell reference is the fastest and most readable solution.

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.