Thanks:  0
Likes:  0

# Thread: Working with Lists and summing data

1. Hello all,
I am looking for a few pointers as to the best way of dealing with this situation.
Say:
Col.A, Col.B, Col.C
Material, Qty, Rate
Cement, 1, 3.00
Sand, 5, 2.50
Cement, 3, 2.50
Cement, 3, 3.00

I would like to total all the Cement, Sand and whatever else may appear but I wish them to be totalled also within discreet Rates.
ie:

Cement, 4, 3.00
Cement, 3, 2.50
Sand, 5, 2.50

I am not aware of which headings will appear nor rates and quantities.
I have contemplated Data, Consolidate - SubTotals and Pivot Tables ... any other suggestions?
These 'summary results' are then to be copied elsewhere on the worksheet.
I trust their is someone out there to help me in the right direction.
Cheers
S.

Sorry the Columns looked really pretty when I typed it out .. honest!

[ This Message was edited by: Sam40mUK on 2002-04-03 10:28 ]

2. How 'bout using a PivotTable with 'Material' and 'Rate' in the ROW area and 'Sum of Qty' in the DATA area.

******>
 Sum of Qty Material Rate Total Cement 2.50 3 3.00 4 Sand 2.50 5

[ This Message was edited by: Mark W. on 2002-04-03 10:38 ]

3. Hello Mark,
Thanks for the swift response.
I have tried your suggestion and it seems to work a treat.
For future refernce is a PT the only way of going about this kind of thing?
Cheers again.
S.

[ This Message was edited by: Sam40mUK on 2002-04-03 11:00 ]

4. Another alternative is SumProduct
I used range names but that is not necesary.
You can extend the totals if you want with

=Qty*Rate

Grand total is =SUMPRODUCT((Qty*Rate))

With categories cement and sand in A9 and A10, the following gives total by category

=SUMPRODUCT((Material=A9)*(Qty)*(Rate))
=SUMPRODUCT((Material=A10)*(Qty)*(Rate))

5. On 2002-04-03 10:57, Sam40mUK wrote:
Hello Mark,
Thanks for the swift response.
I have tried your suggestion and it seems to work a treat.
For future refernce is a PT the only way of going about this kind of thing?
Cheers again.
S.

[ This Message was edited by: Sam40mUK on 2002-04-03 11:00 ]
I wouldn't be so bold as to claim that a PivotTable is the only way, but it certainly is an easy way.

[ This Message was edited by: Mark W. on 2002-04-03 12:22 ]

6. On 2002-04-03 12:22, Mark W. wrote:
On 2002-04-03 10:57, Sam40mUK wrote:
Hello Mark,
Thanks for the swift response.
I have tried your suggestion and it seems to work a treat.
For future refernce is a PT the only way of going about this kind of thing?
Cheers again.
S.

[ This Message was edited by: Sam40mUK on 2002-04-03 11:00 ]
I wouldn't be so bold as to claim that a PivotTable is the only way, but it certainly is an easy way.

[ This Message was edited by: Mark W. on 2002-04-03 12:22 ]
I definitely like the answer.

7. Thanx for all your replies I see the way a little clearer now
Cheers
S.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•