+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT across Multiple Sheets in the same workbook

  1. #1
    Registered User
    Join Date
    09-17-2007
    Posts
    2

    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

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    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

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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)))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    09-17-2007
    Posts
    2

    Works

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1