To add every value that matches a lookup criterion in Excel, use SUMIF. With product names in A2:A10, amounts in B2:B10, and the product to find in E2, enter =SUMIF(A2:A10,E2,B2:B10). It totals all matching rows, including duplicates; use SUMIFS when the total must meet more than one condition.
What “sum lookup” means in Excel
“Sum lookup” is not the name of a single Excel function. People usually mean one of two tasks: adding values from every row that matches a search, or finding one matching row and returning its value. Choosing the right task matters. A lookup function such as XLOOKUP normally returns one matching result, while SUMIF and SUMIFS calculate a total across matching rows.
For example, a sales list might contain repeated entries for the same product. If you want the combined sales for Tea, you need a conditional sum, not a lookup that stops at the first Tea row. This distinction also applies to customer orders, expense categories, inventory transactions, and any table where a key appears more than once.
Before building a formula, identify three things: which cells contain the values to add, which cells contain the labels or keys to search, and what criterion should count as a match. Keeping those ranges aligned and using the appropriate function prevents many common errors.
How SUMIF works
SUMIF has three arguments in this order: the range to check, the criterion, and the range to add. The first and third ranges should cover corresponding rows. If you leave out the optional sum range, Excel adds the cells in the criteria range itself, which is useful when the numbers and criteria are in the same column.
Suppose A2:A10 contains product names and B2:B10 contains amounts. If the rows labeled Tea have amounts of 20, 35, and 70, the formula below returns 125. E2 contains Tea.
With product names in A2:A10, amounts in B2:B10, and the requested product in E2, use:
=SUMIF(A2:A10,E2,B2:B10)
The formula checks each product name against E2 and adds the amount from the same row whenever it matches. It does not need the matching rows to be next to one another. If Tea appears in several places, each matching amount is included.
You can also enter the criterion directly in a formula. With the same product and amount ranges, this version sums rows labeled Tea:
=SUMIF(A2:A10,"Tea",B2:B10)
A cell reference is generally easier to reuse: change E2 to another product and the result updates without editing the formula. In Microsoft 365 for Windows, type the formula in a result cell and press Enter. Excel for Mac uses the same function and formula structure; interface details and keyboard shortcuts can differ.
Sum using more than one condition
Use SUMIFS when a row must satisfy multiple conditions. Its argument order differs from SUMIF: the first argument is the sum range, followed by pairs of criteria ranges and criteria. That change is important when adapting a formula; putting the ranges in SUMIF order can produce an incorrect result.
For example, imagine amounts in B2:B100, product names in A2:A100, and regions in C2:C100. E2 contains Tea and F2 contains West. If the Tea sales in the West region are 40 and 25, the formula returns 65.
With amounts in B2:B100, products in A2:A100, regions in C2:C100, the product criterion in E2, and the region criterion in F2, use:
=SUMIFS(B2:B100,A2:A100,E2,C2:C100,F2)
You can add further criteria-range and criterion pairs for conditions such as salesperson, status, or a date boundary. For a total limited to a particular period, a start-date condition and an end-date condition can both be applied to the same date column. This is often more reliable than trying to match a date displayed in a particular text format.
When a lookup function is the better choice
If each key is unique and you want the value from one corresponding row, a lookup function may be more direct. In Microsoft 365, XLOOKUP searches a range and returns the corresponding item from another range. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not supported in some older Excel versions.
With product names in A2:A10, amounts in B2:B10, and the requested product in E2, this formula returns the amount for the first matching product, or 0 if there is no match:
=XLOOKUP(E2,A2:A10,B2:B10,0)
That is not the same as summing duplicates. If Tea appears three times, XLOOKUP returns the amount from one matching row; SUMIF adds all three. If the data is guaranteed to have one row per key, XLOOKUP can be suitable. If repeated keys are meaningful and all should contribute, use SUMIF or SUMIFS instead.
In Microsoft 365, FILTER can return every matching value, and SUM can add those results. This is another way to express a conditional total, but it is usually less concise than SUMIF for a straightforward one-condition sum. With product names in A2:A100, amounts in B2:B100, and the requested product in E2, use:
=SUM(FILTER(B2:B100,A2:A100=E2,0))
The final argument makes the formula return 0 when there are no matching rows. FILTER requires Microsoft 365 or Excel 2021 or later. For ordinary totals, SUMIF and SUMIFS are broadly understood and make the intention clear.
Text criteria, wildcards, and partial matches
SUMIF and SUMIFS can match text exactly or use wildcard characters. An asterisk represents any number of characters, while a question mark represents one character. Use a tilde before a wildcard character when you mean to match the literal asterisk or question mark rather than use it as a pattern.
For example, a criterion of North* can match text beginning with North, such as North and Northwest. The match is not case-sensitive, so SUMIF does not distinguish between Tea and TEA. If case matters, these functions alone are not enough; a case-sensitive method requires a more specialized formula.
With descriptions in A2:A20, amounts in B2:B20, and a criterion to find any description containing the word repair, use:
=SUMIF(A2:A20,"*repair*",B2:B20)
This can be useful when descriptions contain extra words, such as “repair parts” or “urgent repair.” Check that a partial match will not include unrelated entries. For instance, searching for a short fragment can match more categories than intended.
Dates and numeric criteria
For numerical conditions, criteria can express comparisons such as greater than, less than, or equal to. When the comparison operator is combined with a value held in a cell, join the operator and cell reference with an ampersand. This lets the threshold change without rewriting the formula.
With dates in A2:A100, amounts in B2:B100, and a start date in E2, this formula adds amounts dated on or after the start date:
=SUMIF(A2:A100,">="&E2,B2:B100)
Excel stores genuine dates as numbers, so the cells in the date range should contain dates, not text that merely looks like a date. To total a full month, use a start boundary and an exclusive boundary at the first day of the next month; this avoids missing entries that include times as well as dates. SUMIFS can apply both boundaries to the same date range.
With dates in A2:A100, amounts in B2:B100, a first-of-month start date in E2, and the first day of the next month in F2, use:
=SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2)
This includes dates from E2 onward but excludes F2 and anything later. The approach works even if date cells contain time values, because all times on the final day remain earlier than midnight at the next month’s start.
Common problems and how to check them
A result of zero does not always mean no matching records exist. The criterion might contain an extra space, a spelling difference, or a value stored as text in one place and as a number in another. Check a few source rows directly, confirm the criterion cell has the expected content, and verify that the criteria and sum ranges cover the same rows.
- Ranges are misaligned: If the criteria range begins on row 2 but the sum range begins on row 3, Excel pairs values from different rows. Make corresponding ranges start and end on the same rows.
- Numbers are stored as text: A cell that looks numeric may not behave like a number in calculations. Check the source data and convert text values where necessary.
- Unexpected duplicate totals: SUMIF includes every match. If the total seems too large, check whether the key appears multiple times and whether that is intended.
- Dates are text: A text date may not satisfy a date comparison as expected. Use genuine Excel date values and check the cells’ underlying contents.
- Wildcards match too much: A pattern such as *art* can match many unrelated words. Narrow the criterion or use exact matching when appropriate.
When a result is unexpected, temporarily filter the source table to the criterion and add the visible matching amounts independently. This helps distinguish a formula problem from data-quality issues. Also check whether the formula references the intended criterion cell; copying a formula can shift relative references unless you lock a range with dollar signs.
Choosing the right formula
Use the structure of the data and the question to choose. For a total of all matching rows, SUMIF handles one condition and SUMIFS handles multiple conditions. For the value associated with one unique key, XLOOKUP is often clearer. For a list of all matching records, FILTER may be useful in supported Excel versions.
- One condition and a total: SUMIF.
- Two or more conditions and a total: SUMIFS.
- One matching value from a unique key: XLOOKUP, if available.
- All matching rows displayed as results: FILTER in Microsoft 365 or Excel 2021 and later.
In an older Excel version without XLOOKUP or FILTER, SUMIF and SUMIFS remain suitable for conditional totals. Other lookup approaches can return a single match, but they do not automatically change the requirement from “find one result” to “add every matching result.” The central question is whether duplicates should be combined or whether only one row is wanted.
Frequently Asked Questions
Can I use VLOOKUP to sum matching values?
VLOOKUP returns a value from a matching row; it does not total every row with the same key. Use SUMIF for a one-condition total or SUMIFS for multiple conditions.
Does SUMIF add every duplicate match?
Yes. SUMIF adds the sum-range values for every row that meets its criterion. If you only want one matching row, use a lookup function and make sure your key is unique or define which match should be returned.
What is the difference between SUMIF and SUMIFS?
SUMIF applies one condition and puts the criteria range first. SUMIFS supports multiple conditions and puts the sum range first. That difference in argument order is a frequent source of formula mistakes.
Can SUMIF match part of a text value?
Yes. Use an asterisk for any number of characters or a question mark for one character. For instance, *repair* matches text containing “repair.” Wildcard matching is not case-sensitive.
Why does my SUMIF formula return zero?
Check the spelling and spacing of the criterion, confirm that the criteria and sum ranges are aligned, and look for numbers or dates stored as text. A mismatch between the cell value and the criterion is a common cause.
Related
- Record Data in Excel Online: Setup, Sharing and Tips
- Gujarati Data in Excel: Typing, Sorting, and Importing
- Excel Data Analysis: How to Find Useful Insights
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- AVERAGEIF in Excel: Average Numbers by Text CriteriaYes. In Excel, AVERAGEIF can use a text criterion—such as a department name or status—to select which rows…
- SUMIF Excel Criteria: Syntax, Examples, and Common FixesIn Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with…
- Transpose Formula Not Spreading? Excel FixesIf an Excel TRANSPOSE formula is not spreading, check that you entered it in a version with dynamic…
- Excel SUMIF: Sum Columns When a Cell Contains TextTo add values in one column only when the corresponding cell in another column is exactly x, use…
- Excel SUM Based on Another Column: Formulas and ExamplesTo sum numbers in Excel according to a value in another column, use SUMIF: with categories in A2:A5…
- Excel PMT Function: Calculate Loan PaymentsTo calculate a fixed loan payment in Excel, divide the annual interest rate by the number of payments…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.