Excel SUMIF Based on Cell Value: Formula & Examples

Formulas & functions · Updated November 22, 2026

The direct answer: use =SUMIF(range, criteria, [sum_range]), where range is the cells you check against a condition, criteria is the value or expression that decides which cells to add, and sum_range is the actual cells to total. If you omit sum_range, Excel sums the cells in range itself.

What SUMIF Actually Does

SUMIF is a conditional summing function. It scans a range of cells, tests each one against a criterion, and then adds up corresponding values from another range. The classic example: you have a list of products in column A and their sales in column B. To total sales for only “Widgets”, you write =SUMIF(A2:A20, "Widgets", B2:B20). The function checks each cell in A2:A20; when it finds “Widgets”, it adds the matching value from B2:B20.

You can also reference a cell as the criteria. Instead of typing “Widgets” in the formula, put the product name in cell D1 and use =SUMIF(A2:A20, D1, B2:B20). This is the “based on cell value” part — when you change D1, the sum updates automatically. This is far more flexible than hardcoding values.

How to Write the Criteria (Text, Numbers, Wildcards)

The criteria argument accepts several forms:

  • Exact match text: "Widgets" or a cell reference like D1.
  • Numeric comparison: ">100", "<50", ">=200". Note the quotes around operators.
  • Wildcards: "*Widget*" matches any text containing "Widget". "W?dget" matches any single character in the ? position.
  • Cell reference with operator: ">"&D1 — the ampersand joins the operator with the cell value.

For example, with amounts in B2:B20 and a threshold in D1, =SUMIF(B2:B20, ">"&D1) sums all values greater than the number in D1. This works because the ampersand creates a text string like ">500".

Choosing Between SUMIF and SUMIFS

SUMIF handles one condition. If you need two or more conditions, use SUMIFS. The syntax is different: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Notice the sum range comes first in SUMIFS.

Example: sum sales in B2:B20 where product is "Widgets" (A2:A20) and region is "East" (C2:C20): =SUMIFS(B2:B20, A2:A20, "Widgets", C2:C20, "East"). A common mistake is using SUMIF with multiple criteria ranges — it won't work. SUMIFS is also slightly faster on large datasets.

Common Problems and Fixes

  • Text vs. number mismatch: If your criteria cell contains a number but the range has text-formatted numbers, SUMIF returns 0. Fix by ensuring both are the same data type.
  • Leading/trailing spaces: A space in the criteria or in the range cells breaks matching. Use =TRIM() on the criteria cell or clean the data.
  • Criteria cell is blank: If D1 is empty, SUMIF treats it as zero and may sum nothing. Use =IF(D1="","",SUMIF(...)) to handle blanks.
  • Whole-column references: =SUMIF(A:A, D1, B:B) works but can slow down large workbooks. Prefer explicit ranges like A2:A10000.
  • Case sensitivity: SUMIF is not case-sensitive. "widget" matches "WIDGET". If you need case-sensitive matching, use SUMPRODUCT with EXACT.

Using Cell Values in Criteria with Operators

Often you want to sum based on a threshold stored in a cell. The correct pattern is =SUMIF(B2:B20, ">"&D1). The quotes around the operator and the ampersand are essential. Without the ampersand, ">D1" would be treated as literal text, not a reference. Similarly, for dates, use =SUMIF(C2:C20, ">="&DATE(2025,1,1), B2:B20) to sum sales on or after January 1, 2025. The DATE function returns a serial number, and the concatenation works because Excel converts the date to its numeric form.

Alternatives: SUMPRODUCT and Dynamic Arrays

For more complex criteria, SUMPRODUCT is powerful: =SUMPRODUCT((A2:A20="Widgets")*(B2:B20)). This multiplies a TRUE/FALSE array by the values, effectively summing only where the condition is TRUE. It handles multiple conditions without SUMIFS' syntax and can do case-sensitive matching with EXACT.

In Excel 365, you can also use FILTER: =SUM(FILTER(B2:B20, A2:A20="Widgets")). This spills the filtered array and sums it. It's more readable but slightly slower on huge datasets. For a single condition, SUMIF remains the simplest and fastest.

Frequently Asked Questions

Can SUMIF sum based on another cell's value without typing the criteria? Yes, put the criteria in a cell (e.g., D1) and use =SUMIF(A2:A20, D1, B2:B20). Change D1 and the sum updates.

Why does my SUMIF return 0 when the criteria cell has a number? Check if the range cells are stored as text (green triangle in corner). Convert them to numbers using Text to Columns or multiply by 1. Also check for invisible spaces.

How do I sum if a cell contains a partial match? Use wildcards: =SUMIF(A2:A20, "*"&D1&"*", B2:B20) sums all rows where A contains whatever is in D1.

Is SUMIF case-sensitive? No. For case-sensitive matching, use =SUMPRODUCT(--EXACT(A2:A20, D1), B2:B20).

Can I use SUMIF across multiple sheets? Not directly. Use multiple SUMIFs added together, or use SUMPRODUCT with 3D references in newer Excel versions.

What's the difference between SUMIF and SUMIFS for a single condition? Functionally they return the same result, but SUMIFS has the sum range first and is designed for multiple criteria. For one condition, SUMIF is shorter to type.

Practical Example with Steps

Assume you have data in A2:C20: Column A = Product, Column B = Region, Column C = Sales. You want total sales for "Widgets" in the "East" region, with the product name in E1 and region in F1.

  1. In cell G1, type: =SUMIFS(C2:C20, A2:A20, E1, B2:B20, F1)
  2. Press Enter. The result shows the sum of sales where both conditions match.
  3. Change E1 or F1 to see the sum update instantly.

If you only need one condition, use =SUMIF(A2:A20, E1, C2:C20). Always ensure the criteria ranges and sum range have the same number of rows.

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.