Matrix and calculations

[Michel Khennafi]

Good evening to all of you...

Is it a challenge? Check this out

I would like to perform some calculations in a spreadsheet where I have a
matrix, for instance (column A= group, column b= subgroup, column C=turnover
and the values in A1=1, B1=3...)


A B C D E
1 1 3 100
2 1 3 100
3 1 2 50
4 1 2 50
5 2 1 100
6 2 1 100
7 3 2 100

I would like to create a summary table using a matrix and have the following

Group Sub Group Occurences Turnover
1 3 2 200 --> (C1=100
+ D1= 100)
1 2 2 100 --> (C3=50 +
C4= 50)
2 1 2 200
3 2 1 100

Is there a way using a matrix to calculate the turnover for a given
combination group / Sub/group.... I know how to calculate the occurence
using a matrix, I would like some help on summing the turnovers

Regards,

Michel

 

[Michael R Middleton]

Michel -

Looks like a straightforward Pivot Table task.

- Mike

www.mikemiddleton.com

++++++++++++++++++++

 

[Max]

A formula way might be to use SUMPRODUCT ..

Assume this table below is
in Sheet1, in A1:C8, data from row2 down

Group SubGroup Turnover
1 3 100
1 3 100
1 2 50
1 2 50
2 1 100
2 1 100
3 2 100

In Sheet2
-------------
You have setup the table below in cols A to D,
data from row2 down

Group SubGroup Occurences Turnover
1 3
1 2
2 1
3 2

For Occurrences
Put in C1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!$B$2:$B$8=$B2))

For Turnover
Put in D1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!$B$2:$B$8=$B2),Sheet1!$C$2:$C$8)

Select C11, fill down to populate the table

For the sample table in Sheet1, you'll get the results:

Group SubGroup Occurrences Turnover
1 3 2 200
1 2 2 100
2 1 2 200
3 2 1 100

Adapt the ranges to suit

 

[Dana DeLouis]

This might get you started...

PivotTable reports 101
http://office.microsoft.com/en-us/assistance/HA010346321033.aspx

 

[Michel Khennafi]

Is there a way to get the results without having a pivot table?

Michel

 

[Max]

Is there a way to get the results without having a pivot table?

The below was suggested in response earlier ??

A formula way might be to use SUMPRODUCT ..

Assume this table below is
in Sheet1, in A1:C8, data from row2 down

Group SubGroup Turnover
1 3 100
1 3 100
1 2 50
1 2 50
2 1 100
2 1 100
3 2 100

In Sheet2
-------------
You have setup the table below in cols A to D,
data from row2 down

Group SubGroup Occurences Turnover
1 3
1 2
2 1
3 2

For Occurrences
Put in C1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!$B$2:$B$8=$B2))

For Turnover
Put in D1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!B$2:$B$8=$B2),Sheet1!$C$2:$C$8)

Select C11, fill down to populate the table

For the sample table in Sheet1, you'll get the results:

Group SubGroup Occurrences Turnover
1 3 2 200
1 2 2 100
2 1 2 200
3 2 1 100

Adapt the ranges to suit