Working with Lists and summing data

Sam40mUK · Apr 3rd, 2002, 11:22 AM

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 ]

Mark W. · Apr 3rd, 2002, 11:32 AM

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 ]

Sam40mUK · Apr 3rd, 2002, 11:57 AM

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 ]

Dave Patton · Apr 3rd, 2002, 12:14 PM

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))

Mark W. · Apr 3rd, 2002, 01:22 PM

Quote:

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 ]

Aladin Akyurek · Apr 3rd, 2002, 01:23 PM

Quote:

On 2002-04-03 12:22, Mark W. wrote:

Quote:

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.

Sam40mUK · Apr 3rd, 2002, 01:54 PM

Thanx for all your replies I see the way a little clearer now

Cheers
S.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 11:22 AM	#1
Sam40mUK Board Regular Join Date: Mar 2002 Location: North Wales Coast, UK Posts: 95	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 ]

Apr 3rd, 2002, 11:57 AM	#3
Sam40mUK Board Regular Join Date: Mar 2002 Location: North Wales Coast, UK Posts: 95	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 ]

Apr 3rd, 2002, 12:14 PM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Another alternative is SumProduct I used range names but that is not necesary. You can extend the totals if you want with =QtyRate Grand total is =SUMPRODUCT((QtyRate)) 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))

Apr 3rd, 2002, 01:54 PM	#7
Sam40mUK Board Regular Join Date: Mar 2002 Location: North Wales Coast, UK Posts: 95	Thanx for all your replies I see the way a little clearer now Cheers S.