Sum total costs for multiple entries

**Dark_Templar** · 05-09-2006, 05:47 PM

Hello,

I added a example.xls :
http://www.geocities.com/dt1337/example.zip

Col A hast a list of (PN)Partnumbers, they are not unique. So some are listed several times.
Each PN has a different price assigned, in Col B.
In Col C I remove any double entries,the PNs are unique.
In D I remove the blanks of Col C.
In Col E, I how often a unique PN of Col D is to be found in the non-unique list in Col A.

(Note: No formulars are in the sheet,i just copied some of the values)

My Problem is, I want to sum the total costs of a unique PN.
Ie. PN 81-1323 is multiple times in Col A and every time has a different cost.
How do I sum up the overall costs per PN?

**Portuga** · 05-10-2006, 09:58 AM

You just need the first 2 columns.
Select them and create a pivot table using that data.

1 - Insert "PN" in the "Row" section of the pivot.
2- Insert "Lookup Total" (in a sum format) in the "Data" section of the pivot.
3 -Done.You will have the total by PN.
4- Whenever you add more values to column "A" and "B", you just need to update the pivot Table and the totals will change automatically.

**Dark_Templar** · 05-10-2006, 11:35 AM

Thx for the reply.
However I don't understand how to add that lookup Total

**Portuga** · 05-10-2006, 11:50 AM

I dont know if you ever created a pivot tble or not so:

1 - Select Rows "A" and "B"
2- go to "Data" and select "Pivot Table and..." and click "Finish"
3 - You should have a pivot in a new worksheet. "Right click" on it select "Wizard..." and "Layout"
4- Drag PN to the "Row" section an drag "Lookup Total" to the data section.
5- You should have the pivot.
"Right click" on the "Count of Lookup Total Costs" tab in the pivot, select "Field Settings" and instead of "count" you should choose "Sum"