Working with Lists and summing data

S

Sam40mUK

Board Regular
Joined
Mar 18, 2002
Messages
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
How 'bout using a PivotTable with 'Material' and 'Rate' in the ROW area and 'Sum of Qty' in the DATA area.<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Sum of Qty</td><td> </td><td> </td></tr><tr><td>Material</td><td>Rate </td><td>Total</td></tr><tr><td>Cement</td><td>2.50</td><td>3</td></tr><tr><td> </td><td>3.00</td><td>4</td></tr><tr><td>Sand</td><td>2.50</td><td>5</td></tr></table>
 </body></html>
This message was edited by Mark W. on 2002-04-03 10:38
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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
Click to expand...

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
 
Upvote 0
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
Click to expand...

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
Click to expand...

I definitely like the answer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Hrishi
  • Question
Dynamic Sumproduct
Replies
5
Views
358
Fluff
Fluff
C
  • Question
Comparing 3 tables to a list of numbers, if match, return 4 columns worth of data on another tab
Replies
4
Views
63
alansidman
A
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
392
James006
James006
M
  • Question
SUM and SUBTOTAL unique product item list
Replies
5
Views
1K
shaowu459
shaowu459
J
  • Question
Need a code to sum various sized blocks of data
Replies
3
Views
216
Fluff
Fluff

Forum statistics

Threads
1,214,426
Messages
6,119,411
Members
448,894
Latest member
spenstar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top