Creating an array of Indirect Sums?

[gncfoo]

I'm using Office 365

This may be a little ambitious but what I'm trying to do is sum a specific Cell across multiple sheets, multiply that sum against a number on another sheet in a large array form?

Example:

SumSheet1	SumSheet2	SumSheet3	MultiplySheet
A1	A1	A1	B1
A2	A2	A2	B2
A3	A3	A3	B3
A4	A4	A4	B4

What I'm trying to do is SUMPRODUCT, essentially as:

• Array 1
  • Sum of A1 on Sheets 1-3
  • Sum of A2 on Sheets 1-3
  • Sum of A3 on Sheets 1-3
  • Sum of A4 on Sheets 1-3
• Array 2
  • MultipleSheet!B1:B4

I currently know the formula: =SUMPRODUCT(SUM(INDIRECT("'"&NAMED_SHEET_RANGE&"'!A1")),MultiplySheet!B1) will work for taking the sum of A1 across all sheets and multiplying it by B1, but I need this done for A1:A39 to be multiplied against B1:B39 without copying the formula 39 times if its even possible.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book4
	A	B
1	17	17
2	24	48
3	33	99
4	40	160
5
Sheet5

Cell Formulas
Range	Formula
B1:B4	B1	=A1:A4*MultiplySheet!B1:B4
A1:A4	A1	=SUM(Sheet1:Sheet4!A1)
Dynamic array formulas.

The A formula is a 3-D formula that looks at multiple sheets. The sheets you want to sum must be all together. You do have to drag it down 39 rows though. It's possible that a BYROW function could do it in a single cell, but I don't have that function yet.

The B1 formula is a single-cell formula you don't have to drag down.