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.
Adrian

Adrian

I don't believe that this can be done.

Have a look at
http://www.excelforum.com/showthread...ght=sumproduct

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


rylo

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