# combine similarity matrices

#### sarahfish

##### New Member
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>

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I'm a little confused about your data, is the 3rd table the expected outcome? the sum of each of the row headers?

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

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

Excel 2010
ABCDEFGHI
1ID_350BrotherFatherUncleCousinGrandsonSonNephewGodfather
2Brother
3Father0
4Uncle21
5Cousin040
6Grandson1221
7Son31124
8Nephew112144
9Godfather0633403
10
11ID_351BrotherFatherUncleCousinGrandsonSonNephewGodfather
12Brother
13Father1
14Uncle30
15Cousin312
16Grandson2142
17Son32134
18Nephew222131
19Godfather2212222
20
21AggregateBrotherFatherUncleCousinGrandsonSonNephewGodfather
22Brother0
23Father1
24Uncle5
25Cousin3
26Grandson3
27Son6
28Nephew3
29Godfather2

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
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>

Last edited:
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!!!

Replies
10
Views
207
Replies
3
Views
328
Replies
4
Views
480
Replies
10
Views
231
Replies
1
Views
186

1,196,357
Messages
6,014,772
Members
441,847
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back