Function Problems

Excel Formulas to Return or Total Repeated Lookup Values

Muhammad TalhaMuhammad Talha Nov 20, 2026 868 views

Question details

Find formulas that return each price associated with a repeated fruit name or total the matching prices.

Return or Total Repeated Lookup Values in Excel
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.
Before you start

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.

Solution 1Recommended

Total matching prices with SUMIF

Use SUMIF when you need one combined price for each fruit.

1
Enter the fruit name

On Sheet2, enter a fruit name such as Apple in cell A2.

2
Enter the total formula

In Sheet2 cell B2, enter =SUMIF(Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8) and press Enter.

3
Calculate other totals

Enter additional fruit names below A2, then drag the fill handle from B2 down to calculate each total.

Total matching prices with SUMIF
Check the ranges: The criteria range and sum range should cover the same rows. SUMIF returns 0 if no fruit matches.
Work with repeated values in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your workbook.
  2. 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. 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. 4. Fill down: Add more fruit names in column A and drag the fill handle from B2 down to calculate their totals.
Total prices for repeated fruit names with SUMIFOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.