Creating a table summarizing two categorical columns - Category A and B

[Dat36]

I would like to analyze the data designed in the following format.

	Address 1 (yes=1,no=0)	Address 2 (yes=1,no=0)	Address 3 (yes=1,no=0)	Activity1 (yes=1,no=0)	Activity2 (yes=1,no=0)	Activity3 (yes=1,no=0)
Organization 1	1​	0​	1​	1​	0​	0​
Organization 2	0​	1​	0​	0​	1​	1​
Organization 3	0​	1​	1​	0​	0​	1​

• I have a unstructured table with the above design.
  • This is the data from a questionnaire asked to each organization whether each organization conduct different types of activities in each addresses.
• I have two questions here:
  1. From the above table, I would like to know the way to create the following Table1. The numbers in the Table1 is COUNTing the number of organization.
  2. If no.1 is possible, I also would like to know the way to create the following Table2, which SUMs the number of activities at each address.

Table1:

	Activity1 (yes=1,no=0)	Activity2 (yes=1,no=0)	Activity3 (yes=1,no=0)
Address 1 (yes=1,no=0)	1​	0​	0​
Address 2 (yes=1,no=0)	0​	1​	2​
Address 3 (yes=1,no=0)	1​	0​	1​

Table 2:

	Activity1 (yes=1,no=0)	Activity2 (yes=1,no=0)	Activity3 (yes=1,no=0)
Address 1 (yes=1,no=0)	1​	0​	0​
Address 2 (yes=1,no=0)	0​	1​	1​
Address 3 (yes=1,no=0)	1​	0​	1​

I would really appreciate your help!! Thank you.

 

[Anonymous1378]

I think I know what you are looking for in Table 1, not sure about Table 2, since it's already split by activity. My guess is as follows:

Book1
	A	B	C	D	E	F	G
1		Address 1 (yes=1,no=0)	Address 2 (yes=1,no=0)	Address 3 (yes=1,no=0)	Activity1 (yes=1,no=0)	Activity2 (yes=1,no=0)	Activity3 (yes=1,no=0)
2	Organization 1	1	0	1	1	0	0
3	Organization 2	0	1	0	0	1	1
4	Organization 3	0	1	1	0	0	1
5
6	Table 1	Activity1 (yes=1,no=0)	Activity2 (yes=1,no=0)	Activity3 (yes=1,no=0)
7	Address 1 (yes=1,no=0)	1	0	0
8	Address 2 (yes=1,no=0)	0	1	2
9	Address 3 (yes=1,no=0)	1	0	1
10
11	Table 2	Activity1 (yes=1,no=0)	Activity2 (yes=1,no=0)	Activity3 (yes=1,no=0)
12	Address 1 (yes=1,no=0)	1	0	0
13	Address 2 (yes=1,no=0)	0	1	1
14	Address 3 (yes=1,no=0)	1	0	1
Sheet3

Cell Formulas
Range	Formula
B6:D6	B6	=E1:G1
A7:A9	A7	=TRANSPOSE(B1:D1)
B7:D9	B7	=MMULT(TRANSPOSE(B2:D4),E2:G4)
B11:D11	B11	=E1:G1
A12:A14	A12	=TRANSPOSE(B1:D1)
B12:D14	B12	=SIGN(MMULT(TRANSPOSE(B2:D4),E2:G4))
Dynamic array formulas.

 

[Dat36]

Thank you so much Anonymous1378!!
These formulas seems functioning perfectly. Much appreciated