Count without SUMPRODUCT
 

Im trying to count how may occurrences of a number there are, for a letter
in the same row, for the following table (~{a,b,c}{1,2,3,4,5}~)

|.a c 1 2 3.|
|.b c 2 3 4.|
|.a c 3 4 5.|


The result should be the matrix
1 2 3 4 5
a 1 1 2 1 1
b 0 1 1 1 0
c 1 2 3 2 1


Tanks
Mossi.pt

Count without SUMPRODUCT
 

If your first table is in H1:L3 and the second in A1:F4 try this formula in
B2 copied across and down

=SUMPRODUCT((($H$1:$H$3=$A2)+($I$1:$I$3=$A2)0)*($ J$1:$L$3=B$1))

"Mossi" wrote:


Im trying to count how may occurrences of a number there are, for a letter
in the same row, for the following table (~{a,b,c}{1,2,3,4,5}~)

|.a c 1 2 3.|
|.b c 2 3 4.|
|.a c 3 4 5.|


The result should be the matrix
1 2 3 4 5
a 1 1 2 1 1
b 0 1 1 1 0
c 1 2 3 2 1


Tanks
Mossi.pt

Count without SUMPRODUCT
 

Tanks that was it!
I lost hope on SUMPRODUCT because of the different size of the two matrix,
but it looks like I was wrong!


"daddylonglegs" escreveu:

If your first table is in H1:L3 and the second in A1:F4 try this formula in
B2 copied across and down

=SUMPRODUCT((($H$1:$H$3=$A2)+($I$1:$I$3=$A2)0)*($ J$1:$L$3=B$1))