# SUMPRODUCT across Multiple Sheets in the same workbook

1. ## SUMPRODUCT across Multiple Sheets in the same workbook

Ive been struggling with this and hope one of the smart people in this forum can help me out.

I have a workbook with a Start sheet and an End sheet.
In front of the Start sheet is a Results sheet.
In between the Start and End sheets is a variable number of sheets.
Each sheet from Start to End sheet, including all the sheets in between have the same structure as follows:
A1 has a value
A2 has a value

I use VBA code to insert excel sheets with this the same structure between the Start and the End sheets and have created formulas on the Results sheet to calculate various results.
For instance:
In A1 on the Results sheet I have =SUM(Start:End!A1) which will give me the sum of the values in A1 for all the sheets in between and including Start and End sheet.

Problem:
I would like to apply the same technique to the SUMPRODUCT function as well to change the formula:
=(Start!A1*Start!A2 + InBetween!A1*InBetween!A2 + End!A1*EndA2) to

=SUMPRODUCT(Start:End!A1,Start:End!A2)

However the formula above does not work.
Is there anyone that can help me create a SUMPRODUCT formula where the 2 arrays are spread across multiple sheets with each element of a particular array in the same place on every sheet?

This will allow me to keep that same formula irrespective of the number of sheets that is placed between the Start and the End sheets. I will be eternally greatful and quite impressed if Excel can do this / someone can figure out how to do it.  Register To Reply

I don't believe that this can be done.

Have a look at

There is some discussion of the problem, and some alternatives.

rylo  Register To Reply

3. If you download and install the free Morefunc utility from here: http://xcell05.free.fr/english/

Then you can apply this formula:

=SUMPRODUCT(--(THREED(Start:End!A1)),--(THREED(Start:End!A2)))  Register To Reply

4. ## Works

Thanks! I've also created a UDF with VBA but it's slow with a large set of sheets  Register To Reply