Calculation Issues

Sum Prices for Matching Names and Show Each Total Once in Excel

Khadija KhanKhadija Khan Nov 18, 2026 868 views

Question details

Calculate the total price for each name and display it only on that name's first occurrence in a list.

Sum Prices for Matching Names and Show Each Total Once in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains repeated names and corresponding prices, and the user wants one total per name within the original list.
Observed behavior
Each name's total should appear next to its first occurrence, with later occurrences left blank.
Before you start

Identify the name and price columns and the first and last rows of your data. The example uses names in A2:A12 and prices in D2:D12; adjust both ranges to match your list.

Solution 1Recommended

Combine COUNTIF and SUMIF to Show Each Name's Total Once

Use this formula when you want totals beside the first occurrence of each name without changing the original list.

COUNTIF checks whether the current row is the first occurrence of a name. If it is, SUMIF adds all prices associated with that name; otherwise, the formula returns an empty string.

1
Choose a result column

In Excel, select an empty cell beside the first data row, such as E2. This example assumes names are in column A and prices are in column D.

2
Enter the formula

In E2, enter =IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$12,A2,$D$2:$D$12),"") and press Enter.

3
Fill down the list

Select E2 and drag its fill handle down through E12. The total appears only beside the first occurrence of each name.

4
Adjust the ranges

If your data extends beyond row 12 or uses different columns, edit the name and price ranges in E2, then fill the updated formula down. Keep the SUMIF ranges the same size.

Combine COUNTIF and SUMIF to Show Each Name's Total Once
First occurrence depends on row order: The total appears on the first matching row in the current list. If you sort the list, the row that displays the total may change.
WPS Spreadsheet

Show One Total per Name in WPS Spreadsheet

Use the same COUNTIF and SUMIF formula in WPS Spreadsheet to total matching prices and display each total only on the first matching row.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Select a result cell: Click E2, or another empty cell beside your first data row. The example uses names in A2:A12 and prices in D2:D12.
  3. 3. Enter the formula: Type =IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$12,A2,$D$2:$D$12),"") and press Enter. Adjust the ranges to fit your data.
  4. 4. Fill the formula down: Select the formula cell and drag its fill handle to the last data row to show each total only once.
Calculate one total per name within the original list.Open and save Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and a familiar interface.Work with spreadsheets in a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula show a total only on the first row for each name?

COUNTIF($A$2:A2,A2) counts occurrences from the first data row through the current row. The IF function runs SUMIF only when that count is 1.

How do I extend the formula to more rows?

Change both SUMIF ranges to include the last data row, such as $A$2:$A$500 and $D$2:$D$500, then fill the formula down to row 500.

Can I total just one specific name?

Yes. Enter =SUMIF(A2:A500,"name",D2:D500) in an empty cell, replacing "name" with the name you want or with a cell reference.

What happens if I sort the list?

The formula identifies the first occurrence in the current row order, so sorting can move the displayed total to a different row for that name.