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 likeD1. - 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.
- In cell G1, type:
=SUMIFS(C2:C20, A2:A20, E1, B2:B20, F1) - Press Enter. The result shows the sum of sales where both conditions match.
- 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
- Excel Power Query Ungroup: How to Split Grouped Rows
- Excel Power Query URL: How to Import Data from a Web Page
- Excel Power Query Transpose Data: Steps & Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Arduino Pass String to Function: Examples, Types, and SafetyYes. In Arduino C++, pass a String to a function by putting its type in the parameter list,…
- COUNTA Function in Excel: Count Nonblank Cells CorrectlyTo count nonblank cells in Excel, use COUNTA. With five entries and two truly empty cells in A2:A8,…
- Excel VLOOKUP Yes or No: How to Return a Match ResultYes. In Excel for Microsoft 365, you can make VLOOKUP return “Yes” or “No” by wrapping an exact-match…
- VLOOKUP to Find Duplicates in Two Worksheets in ExcelTo find values shared by two worksheets, enter a VLOOKUP formula beside the values on the first sheet…
- SUMPRODUCT in Excel: Examples, Criteria, and TipsA simple example of SUMPRODUCT in Excel is =SUMPRODUCT(B2:B5,C2:C5): if B2:B5 contains quantities and C2:C5 contains unit prices,…
- Excel Goal Seek Formula: How to Loop ItExcel does not have a worksheet function called LOOP or GOALSEEK: Goal Seek is a built-in What-If Analysis…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.