Colum totals based on ID and Pivot Table assistance

[robertguy]

Hi,

What I’m trying to do is add the totals in columns D, E & F which are assciated to the ID in column A.

As you will appreciate each ID in column A may have 1 or more rows of data in columns D, E & F so ideally I would like the totals in colum G i.e. on the same row as the ID – see attached file

In addition could any one advise me of how to set-up the pivout table which I have manually created on the Pivot Table Required tab

Any assistance would be gratel appreciated

Many thanks in advance


Rob
N.B. Excel version 2007

[ben_hensel]

What you're saying doesn't match what you're showing us.

You don't have the sum of everything with the same ID as in column A, you summed up everything with the same color formatting, which has like one of each copy of the stuff in column A.

Is that what's driving these "1519", "2163", and "4371" numbers? I think those numbers implicitly correspond to those colors but that's a guess.

Anyway, to create the pivot table:
Select the data table you're working on, click "insert pivot table", and then "ID" as the "Row Label", and insert the three different Prices in the "Values" field and make sure they're all set to "Sum of". Then go over to "Pivot Table: Design" and set the "Grand Totals" to "Rows Only."

Assuming you sort out the ID vs. COP Number thing, that should get you about 95% of the way to the Pivot Table you want.

[newdoverman]

Select column A then click on Find and Select and choose Blanks. Click OK. Immediately hit the = key then the UP arrow and Ctrl + Enter. This will fill column A with the values required for the Pivot table.

Create the Pivot table.....result shown in this workbook.

[robertguy]

ben_hensel

Many thanks for your prompr reply and sorry to be so vague so here goes again..

The formula should look at colum ID for the first ID e.g. 1519 and the add up all the corresponding data in columns D, E & F so in this case all the all the data in columns D, E & F until it finds the next ID e.g. 2163.

The totals for each ID should then be input into the corresponding cell against the ID in column G

Hope this helps

Rob

[robertguy]

newdoverman

Many thanks for your prompt reply and example of the pivot table... I think I was going wrong as the ID only appeared once in column A (in my example) where as your example shows it should be copied down each relevant row until the next ID appears.

Thanks again

Rob

[newdoverman]

The Pivot table and calculations based on the values in the first column require that each row of data have all the relevant data. In other words it has to be a "Proper Data Set" of one row per record and one column per field...preferably without blanks and when it comes to crucial fields like record ID's ...no blanks.