summing arrays

[Ray S.]

Is it possible to sum two arrays? I have a large array with specific total
amounts for each item in each column and row of the array. Along column A
there are 40 rows of specific finance items. Columns B through X identify
specific business entities. The points at which the rows and columns cross
contain dollar amounts. I have two such large files and I want to essentially
combine them. They both have the same 40 items and the same business
entities, but with different dollar amounts. Is there a formula or function
whereby I can add the two together?

 

[Lars-Åke Aspelin]

Is it possible to sum two arrays? I have a large array with specific total
amounts for each item in each column and row of the array. Along column A
there are 40 rows of specific finance items. Columns B through X identify
specific business entities. The points at which the rows and columns cross
contain dollar amounts. I have two such large files and I want to essentially
combine them. They both have the same 40 items and the same business
entities, but with different dollar amounts. Is there a formula or function
whereby I can add the two together?

Is this to be done just once, or do you want to keep and update the
two files with automatic update of the sum?

In the first case you can select cells B2:X41 in the first sheet and
do "Copy". Then select cell B2 in the second cell and do "Paste
Special" with Operation set to "Add".

In the second case you put the following formula in cell B2 of a new
sheet:

=Sheet1!B2+Sheet2!B2

where Sheet1 and Sheet2 are the names of you two sheets with data.
Then copy this formula to the right thru cells C2:X2
Then copy cells B2:X2 down thru B3:X41

Hope this helps / Lars-Åke

 

[Herbert Seidenberg]

Excel 2007
Consolidate two arrays.
Arrays can be of different sizes, in any location,
and with row/column items in any order.
Output can be arranged, filtered and sorted multiple ways.
All without formulas or code.
All dynamically.
http://www.mediafire.com/file/myonnobmliz/01_24_09.xlsx

 

[Ray S.]

Wow! That solution is so cool that I think I'll go out and buy 2007. I'm
working with 2003.

 

[Ray S.]

Your first case works fine for me...thanks.

Is this to be done just once, or do you want to keep and update the
two files with automatic update of the sum?

In the first case you can select cells B2:X41 in the first sheet and
do "Copy". Then select cell B2 in the second cell and do "Paste
Special" with Operation set to "Add".

In the second case you put the following formula in cell B2 of a new
sheet:

=Sheet1!B2+Sheet2!B2

where Sheet1 and Sheet2 are the names of you two sheets with data.
Then copy this formula to the right thru cells C2:X2
Then copy cells B2:X2 down thru B3:X41

Hope this helps / Lars-Ã…ke