How to count number of times with table?-CodePudding

Please help me with this question.


The raw data at worksheet1: 

    | Staff | Product1  | Product2  |  Product3 |  ...

ROW1  A     | 19_a_desk | 20_a_desk | 21_d_desk |  

ROW2  B     | 20_c_desk | 21_d_desk |           |  

ROW3  C     | 21_a_desk | 21_c_desk |           |  

ROW4  D     | 19_a_desk | 21_a_desk | 20_d_desk | 

ROW4  E     | 19_a_desk | 20_b_desk |           |
...


The result at worksheet2:


    | Staff | a_desk | b_desk | c_desk | d_desk |...

ROW1  A     |   2    |   0    |    0   |   1    | 

ROW2  C     |   1    |   0    |    1   |   0    | 

ROW3  B     |   0    |   0    |    1   |   1    | 

ROW4  E     |   1    |   1    |    0   |   0    | 

ROW4  D     |   2    |   0    |    0   |   1    | 
...

The description of the question:

1 About the raw data at worksheet1:

(1) The Product column will be more than Product1, Product2, Product3…

(2) The Staff row will be more than A,B,C,D,E…

(3) So the result at worksheet2,

will be more than a_desk, b_desk, c_desk, d_desk...
will be more than A,B,C,D,E...

2 About the result at worksheet2:

(1) The order of staffs might be different from the raw data.

(2) e.g, According to the raw data, Staff A has " 19_a_desk, 20_a_desk, 21_d_desk ". Because Staff A has 2 data(19_a_desk, 20_a_desk) included "a_desk", so it will show 2 in the result worksheet2.

How to write formula for the result to count number of times ?

Please help me with this question. Thank you very much.

CodePudding user response：

Try SUMPRODUCT:

=SUMPRODUCT(($A$1:$A$10=$G2)*(ISNUMBER(SEARCH(H$1,$B$1:$D$10))))

copy to the right and down