Count without SUMPRODUCT

[Guest]

I’m 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

 

[Guest]

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))