How to summarize the total weight...

R

rantz

I need to summarize the total weight of an order number and then put
the result in a new column (called "total order weight") on each row.

I have the columns: "ordernr" and "weight" but I also need the
last row called "total order weight" .

I have like 25000 rows so I don't think it is very sane to do it
manually.

I've tried to use the functions SUBTOTAL and COUNTIF but I can't
seem to to get it right.

ordernr weight total order weight
226224 882 11425
226224 876 11425
226224 880 11425
226224 881 11425
226224 877 11425
226224 880 11425
226224 880 11425
226224 879 11425
226224 879 11425
226224 875 11425
226224 876 11425
226224 880 11425
226224 880 11425
231195 890 890
231702 1267 3800
231702 1268 3800
231702 1265 3800
231779 766 766
231780 1077 1077
 
R

Roger Govier

Hi

Easiest way is to use a Pivot Table

Mark your 2 columns of data
Data>Pivot Table>Next>Layout
Drag Ordernr to Row Area
Drag Weight to Data area
Either click Finish to put the result on a new worksheet, or select the
radio button for Existing Worksheet and say where on the sheet you want the
Pivot table placed

Regards

Roger Govier
 
R

rantz

Yes, I know. I tried that too but that only gives me one row for each
ordernumber like this.

226224 11425
231195 890
231702 3800
231779 766
231780 1077
 
R

Roger Govier

Hi
Then drag weight to the Row area as well, and place it to the right of ordernr

Regards

Roger Govier
 
B

bplumhoff

Hello,

I suggest to write into C1:
=SUMPRODUCT(--(A1=$A$1:$A$19),$B$1:$B$19)
Then copy this down to row 19.

HTH,
Bernd
 
R

rantz

Sorry, that don't give me the right data either. I get sumerize from
all unique weights.

Since there are two items that weights 666 I get total of 1332 like
this.


Order weight total

222245 666 1332
667 1334
668 668
669 2676
670 2010
671 2013
672 3360
673 2019
674 4044
675 2025
676 4056
677 4062
678 5424
679 2037

This is what I want...

222245 666 37060
222245 666 37060
222245 667 37060
222245 667 37060
222245 668 37060
222245 669 37060
222245 669 37060
222245 669 37060
222245 669 37060
222245 670 37060
222245 670 37060
222245 670 37060
222245 671 37060
222245 671 37060
222245 671 37060
222245 672 37060
222245 672 37060
222245 672 37060
222245 672 37060
222245 672 37060
222245 673 37060
222245 673 37060
 
P

Peter Aitken

rantz said:
I need to summarize the total weight of an order number and then put
the result in a new column (called "total order weight") on each row.

I have the columns: "ordernr" and "weight" but I also need the
last row called "total order weight" .

I have like 25000 rows so I don't think it is very sane to do it
manually.

I've tried to use the functions SUBTOTAL and COUNTIF but I can't
seem to to get it right.

ordernr weight total order weight
226224 882 11425
226224 876 11425
226224 880 11425
226224 881 11425
226224 877 11425
226224 880 11425
226224 880 11425
226224 879 11425
226224 879 11425
226224 875 11425
226224 876 11425
226224 880 11425
226224 880 11425
231195 890 890
231702 1267 3800
231702 1268 3800
231702 1265 3800
231779 766 766
231780 1077 1077
Click to expand...

The Subtotals command on the data menu will do it in a flash.
 
Top