+ Reply to Thread
Results 1 to 2 of 2

Alternative to SUMPRODUCT???

  1. #1
    Registered User
    Join Date
    01-15-2009
    Location
    North Carolina
    MS-Off Ver
    Excel 2003
    Posts
    24

    Exclamation Alternative to SUMPRODUCT???

    The formula below appears hundereds of times in my xls and I have a feeling it is bogging down my system (the xls's that use this formula range from 15-50 MB).

    =SUMPRODUCT('Hours Per Unit'!$D$10:OFFSET('Hours Per Unit'!$D$10,ROW($H$62)-6,0),O$6:O$62)-O123

    Any ideas of a more efficient way to write this would be much appreciated.

    Or if you think this is the best -- let me know too. Thanks!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Alternative to SUMPRODUCT???

    How about ...

    =SUMPRODUCT('Hours Per Unit'!$D$10:$D$66, O$6:O$62) - O123

    And if it's that EXACT formula, put the first part in a separate cell and compute it just once, e.g., in A1,

    =SUMPRODUCT('Hours Per Unit'!$D$10:$D$66, O$6:O$62)

    and elsewhere,

    =$A$1 - O123
    Entia non sunt multiplicanda sine necessitate

+ 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