helps matrix table

M

mycarpark

ä¾åº¦æœ‰å¤§é‡é›»è…¦éŠæˆ²

--------------------------------------------------------------------------------
excel question

if i have this table (two col, several rows)

x 1
x 2
x 1
y 1
z 2
y 1
z 2

i want to display in matrix table in excel
x y z
1 2 2 2
2 1 2 1

how to write simple function to display above?
e.g. 1 and x is totally display 2 times.
 
B

Bondi

ä¾åº¦æœ‰å¤§é‡é›»è…¦éŠæˆ²

--------------------------------------------------------------------------------
excel question

if i have this table (two col, several rows)

x 1
x 2
x 1
y 1
z 2
y 1
z 2

i want to display in matrix table in excel
x y z
1 2 2 2
2 1 2 1

how to write simple function to display above?
e.g. 1 and x is totally display 2 times.
Click to expand...

Hi,
If you set up your matrix in say C1 so that D1=X, E1=Y and F1=Z and
C2=1 and C3 = 2.
Asuming your data is in A1:B7 then you could use SumProduct().

=SUMPRODUCT(--($A$1:$A$7=D1),--($B$1:$B$7=$C$2))

Place the formula in D2 and copy out along the row. Careful with the
cell referencing.

Regards,
Bondi
 
Top