Excel Formulas to Return or Total Repeated Lookup Values
Question details
Find formulas that return each price associated with a repeated fruit name or total the matching prices.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet lists fruit names more than once, with a price for each entry, and a second sheet uses fruit names as lookup criteria.
- Observed behavior
- The user wants either a list of individual matching prices or one total for each fruit.
Check that Sheet1 contains fruit names in A2:A8 and numeric prices in B2:B8, and that Sheet2 contains the fruit to find in A2. Adjust the ranges and sheet names in the formulas to match your workbook.
Total matching prices with SUMIF
Use SUMIF when you need one combined price for each fruit.
On Sheet2, enter a fruit name such as Apple in cell A2.
In Sheet2 cell B2, enter =SUMIF(Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8) and press Enter.
Enter additional fruit names below A2, then drag the fill handle from B2 down to calculate each total.

Return each matching price with INDEX and AGGREGATE
Use this formula when you need to display matching prices individually across a row.
Total repeated lookup values in WPS Spreadsheets
Use SUMIF in WPS Spreadsheets to add prices for each repeated fruit name. Open an Excel workbook, enter the formula, and fill it down for additional fruits.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load your workbook.
- 2. Check the source data: On Sheet1, place fruit names in A2:A8 and numeric prices in B2:B8, or identify the equivalent ranges in your workbook.
- 3. Enter the lookup and formula: On Sheet2, enter a fruit name in A2. In B2, enter =SUMIF(Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8) and press Enter.
- 4. Fill down: Add more fruit names in column A and drag the fill handle from B2 down to calculate their totals.

Frequently Asked Questions
How do I add all prices for the same fruit in Excel?
If fruit names are in Sheet1!A2:A8, prices are in Sheet1!B2:B8, and the fruit to find is in A2, enter =SUMIF(Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8).
Why does the INDEX and AGGREGATE formula return a blank?
The formula displays a blank when there are no more matching entries. Check the fruit name in Sheet2!A2 and the source ranges on Sheet1; IFERROR can also hide formula errors.
Can I return matching prices vertically instead of across a row?
Yes. If the fruit is in Sheet2!A2, enter the INDEX and AGGREGATE formula in B2, change its lookup reference to $A$2, and replace COLUMN(A1) with ROW(A1). Drag the formula down to return successive matches.
Why is my SUMIF total lower than expected?
Check that the price cells in Sheet1!B2:B8 contain numbers rather than text, and that the criteria and price ranges include all relevant rows. Also check fruit names for extra spaces.




