the intersection of two matrices

[achaus]

Hello,

This should be relatively simple but I've not succeeded in finding a way to look at the intersection between two matrices

If I have 1 sheet with individuals for rows, and columns a b c whereby values in a b c are binary (0,1)


and a second sheet with the same individuals with columns d e f with binary values for cells


is there a way to create a matrix a b c * d e f where the cell = the number of intersections? i.e. how many individuals for which a * d = 1 and so on?

Thank you for your help in advance.

 

[StephenCrump]

Welcome to the Forum!

I'm not sure what you mean by intersection. Can you post a simple example?

See Part B here for ways you can do this: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[achaus]

Yes, sorry for not being clearer, and thank you.

I hope the screenshot clarifies what I aim to do.

for cell A:E would like to know the number of individuals that have a 1 for A as well as a 1 for E.

Thank you !

https://drive.google.com/open?id=0BwNrSsNUt-_Mb3hIbGtZclRMMW8

 

[StephenCrump]

B8: =SUMPRODUCT(INDEX($B$2:$E$5,,MATCH($A8,$B$1:$E$1,)),INDEX($G$2:$I$5,,MATCH(B$7,$G$1:$I$1,)))

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Person	A	B	C	D		E	F	G
2	1	1	0	1	1		1	0	0
3	2	0	1	0	1		0	0	0
4	3	1	1	1	1		1	1	1
5	4	0	1	1	1		0	1	1
6
7	Output	E	F	G
8	A	2	1	1
9	B	1	2	2
10	C	2	2	2
11	D	2	2	2
Sheet1

 

[achaus]

Hi Stephen,

Thank you very much for your help! I really appreciate it