Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

**alte.ianp** · 01-13-2024, 12:14 PM

ALT Title: Shorten the formula of the sum of Index-Matches of Multiple Alternate/Separate Arrays

Context: I have to add these multiple arrays (matrices) into a one big array (matrix). We add the values of the cells having the same row code/text and the same column code/text for all these multiple arrays to become a one big array (matrix).

My Method: I used multiple INDEX-MATCH formula for each array. This resulted to a very long formula.

Problem: Is it possible to shorten this formula using other Excel functions I am not familiar about?

I hope you will be able to help me with this because I really just want to know if there is a shorte/simpler way to do this. Thank you so much!

**Glenn Kennedy** · 01-13-2024, 01:59 PM

Ugh. That is one nasty formula. Try this. On the basis of VERY limited checks, it looks OK to me:

=IFERROR(INDEX(INDEX($B$1:$M$300,MATCH($V4,$A$1:$A$300,0),),,MATCH(W$3,INDEX($B:$M,AGGREGATE(14,6,ROW($A$1:INDEX($A:$A,MATCH($V4,$A$1:$A$300,0)))/($A$1:INDEX($A:$A,MATCH($V4,$A$1:$A$300,0))=""),1),),0)),0)

in W4, copied across and down.

**alte.ianp** · 01-14-2024, 03:34 AM

I'll try this. Thank you!

**Glenn Kennedy** · 01-15-2024, 05:47 AM

Did it work out for you?

**kvsrinivasamurthy** · 01-15-2024, 07:28 AM

In W4 and copied to full range.

Formula:

Please Login or Register  to view this content.

Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

LinkBack

Thread Tools

Rate This Thread

Display

Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

Re: Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

Re: Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

Re: Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

Re: Combine Multiple Arrays/Matrices (Get the SUM) into a one big Array (Matrix)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Array formula to combine 2 numbers into a matrix

Find Matrix Inverse for 22 and 33 matrices

How to combine 2 arrays of data into 1 array

how to combine two 1d array into a 2d array(i.e. a matrix)?

Possible to make computations on matched arrays in unequal matrices w/ missing values??

[SOLVED] MATRICES ISSUE: Trouble translating some geographical matrix theory into a formula

how to combine named arrays into a matrix

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions