Sum Prices for Matching Names and Show Each Total Once in Excel
Question details
Calculate the total price for each name and display it only on that name's first occurrence in a list.

- 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.
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.
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.
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.
In E2, enter =IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$12,A2,$D$2:$D$12),"") and press Enter.
Select E2 and drag its fill handle down through E12. The total appears only beside the first occurrence of each name.
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.

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. Open the spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
- 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. 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. Fill the formula down: Select the formula cell and drag its fill handle to the last data row to show each total only once.

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.




