Formula for adding up totals of groups

[koba]

Hi

Could somebody please help me with the following problem?

I have a whole heap of raw data that looks like this

Client Product Group Qty $
aaa 1 1 66.39
aaa 1 1 103.78
aaa 1 1 78.8
aaa 1 1 96.96
aaa 2 1 54.17
aaa 2 1 29.74
aaa 2 1 28.66
aaa 2 1 30.53
aaa 2 1 41.49
aaa 2 1 28.78
aaa 3 1 57.1
aaa 4 1 25.42
aaa 4 2 14.86
aaa 4 1 14.99
aaa 4 2 5.99
aaa 4 1 41.6
bbb 1 -1 -130
bbb 1 1 81.1
bbb 1 1 124.48
bbb 1 1 97.82
bbb 2 1 70.99
bbb 2 1 22.18
bbb 2 1 22.66
bbb 2 1 36.09
bbb 2 1 27.62
bbb 2 1 70.99
bbb 2 1 70.99
bbb 2 1 27.62
bbb 2 1 21.5
bbb 3 1 90
bbb 3 1 72
bbb 4 1 27.6
bbb 4 1 35.88
bbb 4 10 148.32
ccc 1 1 87.9
ccc 1 1 79.17
ccc 1 1 100.08
ccc 1 1 80
ccc 1 1 451.05
ccc 1 1 350
ccc 2 7 55
ccc 4 1 12.02
ccc 4 1 8.15
ccc 4 1 41.6
ccc 4 1 11.15
ccc 4 1 14.83
ccc 5 0 0
ccc 5 2 771.62
ccc 48 1 166.6
ccc 48 1 224



I would like to create a formula that adds up the total quantity of client aaa’s sales and in another cell adds up aaa’s the total sales for each product group.

Then I would like to another cell that adds up the total quantity for and total sales for aaa’s product groups 3 & 4. Then I would obviously do this for each client

Please help

Thanks
Andrew

[Bryan Hessey]

If you select the data to be totalled, and then select Data, Subtotals, and select the total options required does that suffice?

Hi

Could somebody please help me with the following problem?

I have a whole heap of raw data that looks like this

Client Product Group Qty $
aaa 1 1 66.39
aaa 1 1 103.78

I would like to create a formula that adds up the total quantity of client aaa’s sales and in another cell adds up aaa’s the total sales for each product group.

Then I would like to another cell that adds up the total quantity for and total sales for aaa’s product groups 3 & 4. Then I would obviously do this for each client

Please help

Thanks
Andrew

[JMay]

Get familiar with Pivot Tables. Once you do, you'll use nothing else for
this sort of thing...
http://peltiertech.com/Excel/Pivots/pivottables.htm

HTH


"koba" <[email protected]> wrote in message
news:[email protected]...
>
> Hi
>
> Could somebody please help me with the following problem?
>
> I have a whole heap of raw data that looks like this
>
> Client Product Group Qty $
> aaa 1 1 66.39
> aaa 1 1 103.78
> aaa 1 1 78.8
> aaa 1 1 96.96
> aaa 2 1 54.17
> aaa 2 1 29.74
> aaa 2 1 28.66
> aaa 2 1 30.53
> aaa 2 1 41.49
> aaa 2 1 28.78
> aaa 3 1 57.1
> aaa 4 1 25.42
> aaa 4 2 14.86
> aaa 4 1 14.99
> aaa 4 2 5.99
> aaa 4 1 41.6
> bbb 1 -1 -130
> bbb 1 1 81.1
> bbb 1 1 124.48
> bbb 1 1 97.82
> bbb 2 1 70.99
> bbb 2 1 22.18
> bbb 2 1 22.66
> bbb 2 1 36.09
> bbb 2 1 27.62
> bbb 2 1 70.99
> bbb 2 1 70.99
> bbb 2 1 27.62
> bbb 2 1 21.5
> bbb 3 1 90
> bbb 3 1 72
> bbb 4 1 27.6
> bbb 4 1 35.88
> bbb 4 10 148.32
> ccc 1 1 87.9
> ccc 1 1 79.17
> ccc 1 1 100.08
> ccc 1 1 80
> ccc 1 1 451.05
> ccc 1 1 350
> ccc 2 7 55
> ccc 4 1 12.02
> ccc 4 1 8.15
> ccc 4 1 41.6
> ccc 4 1 11.15
> ccc 4 1 14.83
> ccc 5 0 0
> ccc 5 2 771.62
> ccc 48 1 166.6
> ccc 48 1 224
>
>
>
> I would like to create a formula that adds up the total quantity of
> client aaa's sales and in another cell adds up aaa's the total sales
> for each product group.
>
> Then I would like to another cell that adds up the total quantity for
> and total sales for aaa's product groups 3 & 4. Then I would obviously
> do this for each client
>
> Please help
>
> Thanks
> Andrew
>
>
> --
> koba
> ------------------------------------------------------------------------
> koba's Profile:
> http://www.excelforum.com/member.php...o&userid=28639
> View this thread: http://www.excelforum.com/showthread...hreadid=487508
>

[Roger Govier]

Hi Andrew

Take a bit of time to learn about Pivot Tables. It will be well rewarded.
You can start by going to
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and to
http://peltiertech.com/Excel/Pivots/pivotstart.htm

I think this will provide all you are looking for.

Regards

Roger Govier


koba wrote:
> Hi
>
> Could somebody please help me with the following problem?
>
> I have a whole heap of raw data that looks like this
>
> Client Product Group Qty $
> aaa 1 1 66.39
> aaa 1 1 103.78
> aaa 1 1 78.8
> aaa 1 1 96.96
> aaa 2 1 54.17
> aaa 2 1 29.74
> aaa 2 1 28.66
> aaa 2 1 30.53
> aaa 2 1 41.49
> aaa 2 1 28.78
> aaa 3 1 57.1
> aaa 4 1 25.42
> aaa 4 2 14.86
> aaa 4 1 14.99
> aaa 4 2 5.99
> aaa 4 1 41.6
> bbb 1 -1 -130
> bbb 1 1 81.1
> bbb 1 1 124.48
> bbb 1 1 97.82
> bbb 2 1 70.99
> bbb 2 1 22.18
> bbb 2 1 22.66
> bbb 2 1 36.09
> bbb 2 1 27.62
> bbb 2 1 70.99
> bbb 2 1 70.99
> bbb 2 1 27.62
> bbb 2 1 21.5
> bbb 3 1 90
> bbb 3 1 72
> bbb 4 1 27.6
> bbb 4 1 35.88
> bbb 4 10 148.32
> ccc 1 1 87.9
> ccc 1 1 79.17
> ccc 1 1 100.08
> ccc 1 1 80
> ccc 1 1 451.05
> ccc 1 1 350
> ccc 2 7 55
> ccc 4 1 12.02
> ccc 4 1 8.15
> ccc 4 1 41.6
> ccc 4 1 11.15
> ccc 4 1 14.83
> ccc 5 0 0
> ccc 5 2 771.62
> ccc 48 1 166.6
> ccc 48 1 224
>
>
>
> I would like to create a formula that adds up the total quantity of
> client aaa’s sales and in another cell adds up aaa’s the total sales
> for each product group.
>
> Then I would like to another cell that adds up the total quantity for
> and total sales for aaa’s product groups 3 & 4. Then I would obviously
> do this for each client
>
> Please help
>
> Thanks
> Andrew
>
>

[koba]

--------------------------------------------------------------------------------

Hi guys

thanks for your help very useful!!!!!!1

I used the pivot table but there is one problem, how do i add product groups together in the pivot table, because right now its showing them all individually????????????

Cheers
Andrew