Excel: Sum VLOOKUP Values Across Multiple Matching Rows

Formulas & functions · Updated November 18, 2026

To total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching keys in A2:A100, amounts in B2:B100, and the key to find in E2, enter =SUMIF(A2:A100,E2,B2:B100). VLOOKUP returns the first matching result; SUMIF adds the values from all matching rows.

Why VLOOKUP Does Not Add Multiple Matches

VLOOKUP is designed to find a value in the first column of a range and return a value from a specified column in the same row. When the lookup key appears more than once, an exact-match VLOOKUP returns the first matching row it finds. It does not continue through the rest of the list and total the other results.

For example, suppose an order table has three rows for item P-104, with amounts of 120, 85, and 40. A VLOOKUP for P-104 returns 120, not the combined total of 245. The issue is not that VLOOKUP has failed: it has returned the first match as designed. The requirement is a sum across matching records, so a conditional-sum function is the better fit.

This distinction matters whenever a lookup key is not unique. Product codes may repeat across orders, employee IDs across time entries, or customer names across invoices. Before choosing a formula, decide whether you need one matching row or every matching row.

Set Up the Data and Identify the Match

Assume each record is on its own row, with the lookup key in column A and the amount to total in column B. The value you want to find is in E2. A small sample could look like this:

  • A2 is P-104 and B2 is 120.
  • A3 is P-205 and B3 is 60.
  • A4 is P-104 and B4 is 85.
  • A5 is P-104 and B5 is 40.
  • E2 contains P-104.

In this example, the matching rows are 2, 4, and 5, and their amounts add up to 245. The formula should examine the key column to find rows equal to E2, then add the corresponding values from column B. Keep the key and amount ranges aligned: A2:A100 and B2:B100 both start and end on the same rows.

Use SUMIF to Total Every Matching Row

With lookup keys in A2:A100, amounts in B2:B100, and the search value in E2, enter =SUMIF(A2:A100,E2,B2:B100). In the sample above, this returns 245.

SUMIF takes three arguments in this order: the range to test, the condition to match, and the range containing the numbers to add. Here, Excel checks each cell in A2:A100 against E2. For every match, it adds the value from the corresponding row in B2:B100. If you omit the third argument, SUMIF adds numbers in the tested range itself, which is usually not what you want when keys and amounts are in separate columns.

To enter the formula in Excel for Microsoft 365 on Windows:

  1. Select the cell where you want the total to appear.
  2. Type the formula, replacing the example ranges or lookup cell if your data uses different locations.
  3. Press Enter. If you copy the formula down for more lookup values, use absolute references for the fixed data ranges if needed.

For example, if each lookup value is listed down column E, use absolute references for the source data so those ranges do not move when you fill the formula down. With keys in A2:A100, amounts in B2:B100, and the first requested key in E2, use =SUMIF($A$2:$A$100,E2,$B$2:$B$100). This returns the total for the key in E2; copying it down changes E2 to E3, E4, and so on, while keeping the source ranges fixed.

Sum Using More Than One Condition with SUMIFS

Sometimes a key alone is not enough. You may need to total one product in one region, or invoices for a customer within a particular category. SUMIFS applies multiple conditions to corresponding rows. Its first argument is the sum range, followed by pairs of criteria ranges and criteria.

Suppose product codes are in A2:A100, regions are in B2:B100, amounts are in C2:C100, the product to match is in E2, and the region is in F2. Use =SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2). This totals only rows where the product equals E2 and the region equals F2. The result is not the product total across all regions; it is the subtotal that meets both conditions.

For a worked example, imagine P-104 has amounts of 120 and 85 in the West, and 40 in the East. If E2 contains P-104 and F2 contains West, the formula returns 205. If you need to add another condition, such as status in column D, add another criteria-range and criteria pair at the end. Make sure each criteria range has the same dimensions as the sum range.

When You Need a VLOOKUP Alongside the Sum

Sometimes the key used for the sum is stored in another table, and you first need to retrieve that key. In that case, VLOOKUP can still be useful as one step, while SUMIF performs the total. For example, a separate product list may map a product name to a product code, and the transaction table stores the code. Retrieve the code, then use that code as SUMIF’s criterion.

With product names in G2:G50, product codes in H2:H50, and the name to look up in J2, use =VLOOKUP(J2,G2:H50,2,FALSE). This returns the code from the first matching row in the product list, provided the name is in the leftmost column of the lookup range. The FALSE argument requests an exact match. If the result appears in K2, use that returned code as the SUMIF criterion.

With transaction codes in A2:A100, transaction amounts in B2:B100, and the retrieved code in K2, use =SUMIF(A2:A100,K2,B2:B100). The result is the sum of all transactions for that code, even if the code occurs many times. If the product-name list itself contains duplicate names that map to different codes, a simple VLOOKUP is ambiguous: it returns the first one. Resolve that data issue or use a more specific key before totaling.

Common Problems and How to Diagnose Them

If the total is zero when you expect a positive result, check whether the lookup value and source keys really match. A code stored as text can differ from a number stored as a number, and extra spaces before or after a key can prevent a match. Compare a few source cells with the lookup cell, and check for inconsistent typing or pasted spaces. Formatting a cell to look like text or a number does not always change the underlying value.

If the total is too large, check for duplicate records and confirm that the sum range contains only the values you intend to add. SUMIF counts every matching row, including repeated entries. That is correct for repeated transactions, but it may reveal duplicated source data if each key was supposed to appear only once.

Other useful checks include:

  • Confirm that the key range and sum range start and end on matching rows.
  • Check that the formula points to the correct lookup cell, especially after copying it.
  • Make sure the values in the sum range are numeric. Numbers stored as text may not be added as expected.
  • Look for broad criteria such as a partial code or wildcard that could match more records than intended.
  • Verify that the cell displaying the result is not formatted to hide decimals or show an unexpected number format.

SUMIF and SUMIFS comparisons are generally not case-sensitive, so they do not distinguish codes that differ only by letter case. If case is meaningful in your data, use a method designed for case-sensitive matching rather than assuming these functions will separate the values.

Choosing Between SUMIF, SUMIFS, and VLOOKUP

Choose the function based on the task, not simply because the task includes the word lookup. Use VLOOKUP when you want one corresponding value from a table and the lookup key is in the table’s first column. Use SUMIF when one condition should determine which values are added. Use SUMIFS when two or more conditions must all be true for a row to be included.

SUMIF and SUMIFS are available in standard desktop versions of Excel, including Microsoft 365. They are often clearer than trying to make a lookup function return several records. They also describe the calculation directly: find records meeting these conditions, then add their amounts.

Excel for the web supports these formulas as well. The formula logic is the same on Mac, though ribbon layouts and some keyboard shortcuts can differ from Windows. If your regional settings use semicolons as formula separators, Excel may require semicolons instead of commas when you enter a formula; use the separator accepted by your installation.

Alternatives for Larger or Repeated Reports

For a one-off total, SUMIF is usually the simplest option. If you need totals for many keys, copy the formula alongside a list of keys. For recurring reports, consider formatting the source data as an Excel table so new rows are easier to include in formulas and reports. You can also use a PivotTable to summarize amounts by product, customer, region, or other fields without writing a separate formula for every category.

A PivotTable is particularly useful when readers need to compare several categories or filter the summary interactively. Select a cell in the source data, then use Insert > PivotTable and place the key field in Rows and the amount field in Values. Check that the Values area summarizes the amount by Sum rather than Count. A PivotTable is a summary tool rather than a replacement for a formula in every situation: formulas are convenient when a report needs a specific total in a fixed cell, while a PivotTable is useful for exploring grouped results.

For a clean audit trail, keep the original records unchanged and put summary formulas or PivotTables in a separate area or worksheet. That makes it easier to compare a reported total with the underlying records and reduces the chance of accidentally summing the wrong column.

Frequently Asked Questions

Can VLOOKUP return and sum all matching rows?
Not with an ordinary VLOOKUP formula. It returns the first match. Use SUMIF for one condition or SUMIFS for multiple conditions to add every qualifying row.

What formula adds values for a lookup value in another column?
Use SUMIF. Put the column containing the lookup keys in its first argument, the lookup value in its second, and the amount range in its third. For example, with keys in A2:A100, amounts in B2:B100, and the criterion in E2, use =SUMIF(A2:A100,E2,B2:B100).

How do I sum values that match two columns?
Use SUMIFS, supplying the sum range first and then a criteria-range and criterion for each condition. Each condition must be true for its row to contribute to the total.

Does SUMIF add duplicate matches?
Yes. It adds the amount from every row meeting the condition, including rows with repeated keys. If duplicates should not be counted, first determine which records are valid or use a summary approach that reflects the intended rule.

Why does SUMIF return zero even though the value appears in the list?
Common causes include extra spaces, text-versus-number mismatches, a wrong range, or numbers stored as text in the sum range. Inspect the underlying cells and confirm the formula’s ranges align row by row.

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.