combine similarity matrices

[sarahfish]

Hi,

I have been trying everything to combine similarity matrices, but cannot get the code to work. I have blank matrix that needs to be filled in with the sum of the cells in all of the above matrices. I pasted below an example of what I mean. The number in brackets on the bottom matrix are what the data SHOULD be...not what I actually have successfully written code for.

The data is symmetrical, and each matrix repeats on every 10th line. I tried doing the operation from an earlier question (to sum every nth line), but could not get it to work. Maybe because the txt got in the way? I'm so lost and desperate! If it helps at all, this is the code I was using that I couldn't get to work...

=SUMPRODUCT((MOD(ROW($B$3:$B$29),10)=0)*($B$3:$B$29))

My data looks like this (except a LOT more cases)! THANK YOU SO MUCH!!!
____________________________________________________________________________________________

ID_350	Brother	Father	Uncle	Cousin	Grandson	Son	Nephew	Godfather
Brother
Father	0
Uncle	2	1
Cousin	0	4	0
Grandson	1	2	2	1
Son	3	1	1	2	4
Nephew	1	1	2	1	4	4
Godfather	0	6	3	3	4	0	3
ID_351	Brother	Father	Uncle	Cousin	Grandson	Son	Nephew	Godfather
Brother
Father	1
Uncle	3	0
Cousin	3	1	2
Grandson	2	1	4	2
Son	3	2	1	3	4
Nephew	2	2	2	1	3	1
Godfather	2	2	1	2	2	2	2
Aggregate	Brother	Father	Uncle	Cousin	Grandson	Son	Nephew	Godfather
Brother
Father	[1]
Uncle	[5]
Cousin	[3]
Grandson	[3]
Son	[6]
Nephew	[3]
Godfather	[2]

<tbody>
</tbody>

 

[Weazel]

I'm a little confused about your data, is the 3rd table the expected outcome? the sum of each of the row headers?

 

[sarahfish]

Very understandable that the data is confusing. And THANK YOU for responding!!

The bottom table is a sum of all the tables above (in my real data I have about 400). The individual tables are a count of each person's responses. I need to sum up how many times EVERYONE from the sample had the same response.

For example, the first person (ID_350) paired 'Father/Brother' 0 times, and the second person (ID_351) paired it one time. So the 'Father/Brother' cell in the bottom matrix should be a total of how many times 'Father/Brother' was paired in the entire sample (in this 2-person sample, it was paired 1 time).

I need to figure out code that will fill in the total for each cell in the bottom table...for 400 people. Hence, the desperation.....

THANK YOU AGAIN!!!!

 

[Weazel]

Probably not the best solution but I'm stuck on an alternative at the moment.

Excel 2010

	A	B	C	D	E	F	G	H	I
1	ID_350	Brother	Father	Uncle	Cousin	Grandson	Son	Nephew	Godfather
2	Brother
3	Father	0
4	Uncle	2	1
5	Cousin	0	4	0
6	Grandson	1	2	2	1
7	Son	3	1	1	2	4
8	Nephew	1	1	2	1	4	4
9	Godfather	0	6	3	3	4	0	3
10
11	ID_351	Brother	Father	Uncle	Cousin	Grandson	Son	Nephew	Godfather
12	Brother
13	Father	1
14	Uncle	3	0
15	Cousin	3	1	2
16	Grandson	2	1	4	2
17	Son	3	2	1	3	4
18	Nephew	2	2	2	1	3	1
19	Godfather	2	2	1	2	2	2	2
20
21	Aggregate	Brother	Father	Uncle	Cousin	Grandson	Son	Nephew	Godfather
22	Brother	0
23	Father	1
24	Uncle	5
25	Cousin	3
26	Grandson	3
27	Son	6
28	Nephew	3
29	Godfather	2

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B22 =SUMIF($A$2:$A$19,A22,$B$2:$I$19) B23 =SUMIF($A$2:$A$19,A23,$B$2:$I$19) B24 =SUMIF($A$2:$A$19,A24,$B$2:$I$19) B25 =SUMIF($A$2:$A$19,A25,$B$2:$I$19) B26 =SUMIF($A$2:$A$19,A26,$B$2:$I$19) B27 =SUMIF($A$2:$A$19,A27,$B$2:$I$19) B28 =SUMIF($A$2:$A$19,A28,$B$2:$I$19) B29 =SUMIF($A$2:$A$19,A29,$B$2:$I$19) <tbody> </tbody>

<tbody>
</tbody>

 

[sarahfish]

Awesome! Thank you!

Do you think it would work with all 400 cases? I was worried that excel limits the number of arguments you can use.

THANKS AGAIN!!!