+ Reply to Thread
Results 1 to 2 of 2

SUM formula arguments using data from multiple cells

Hybrid View

  1. #1
    Registered User
    Join Date
    03-26-2009
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2002
    Posts
    10

    SUM formula arguments using data from multiple cells

    Desired formula =SUM(feb6:feb27!D7)

    there are a few worksheets between feb6 and feb27, the column "D" is constant, however the row number can change from week to week. I want to be able to build the formula going down 50 or 60 rows and have the "7", or row number read into the desired formula.

    I have a column with numbers like so

    7
    11
    15
    19
    23
    27
    33
    38


    I need the formula to read those numbers in, so that the formula will read:

    =SUM(feb6:feb27!d7)
    =SUM(feb6:feb27!d11)
    =SUM(feb6:feb27!d15)
    =SUM(feb6:feb27!d19)
    =SUM(feb6:feb27!d23)
    =SUM(feb6:feb27!d27)

    etc, etc....

    Any help will be much appreciated as it will save me a lot of time.

    Thanks in advance.


    dale

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SUM formula arguments using data from multiple cells

    I don't think you'll be able to do that unfortunately ... it's not very easy to build 3D references with INDIRECT and if you do you need to use volatile SUMPRODUCTs / Arrays.

    I would suggest to keep calcs light etc you create a single "calculation" sheet which simply sums all cells in 3D, eg:

    SUMMARY!A1:
    =SUM(FEB6:FEB27!A1)
    and apply over necessary range

    Your final calcs are then simply:

    =INDEX(SUMMARY!D:D,A1)
    copied down

    where A1 onwards holds 7 etc...

+ 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