+ Reply to Thread
Results 1 to 3 of 3

SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Perth, Western Australia
    MS-Off Ver
    Mac 2011 & Windows 2010
    Posts
    5

    SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

    I need to sum up data on multiple sheets using two criteria, which I've managed to do in two different ways (below). However I'm trying to remove the SUMIFS() function from either of these methods to enable this workbook to function in Excel version 2003 (sadly still used by some of our guys on site).

    I'm also posting this so that there's a simple reference for such a solution (below) if you are using Excel 2007 or later - as I see similar posts on many forums about this, SUMPRODUCT on Multiple Sheets with Multiple Criteria, but they are rarely generic and can be difficult to follow for others.

    To explain the formulae below, on each sheet there's three named columns:
    1 - Critera1Range
    2 - Critera2Range
    3 - SumRange

    Then there's a list of the sheet tab names (again a named range): ListTabs

    Highly recommend anyone to use Named Ranges for these references rather than A4:A20 and B4:B20 type references; especially as if the range changes, say you insert extra rows, the formulae below will not update the references to that range within the INDIRECT function. However if your named ranged updates these formulae will still work.

    Some people would call this a 3D array, with columns, rows and sheets being the 3 Dimensions.

    First I have an Array formula which works fine (for the uninitiated, Array formulae are generated be pressing CTL+SHFT+ENTER when editing the formula)

    {=SUM(SUMIFS(INDIRECT(ListTabs&"!SumRange"),INDIRECT(LstTabs&"!Critera1Range"),Criterion1,INDIRECT(ListTabs&"!Critera2Range"),Criterion2))}

    Second I've a SUMPRODUCT (non-array) formula which also works fine:

    =SUMPRODUCT(SUMIFS(INDIRECT(ListTabs&"!SumRange"),INDIRECT(ListTabs&"!Critera1Range"),Criterion1,INDIRECT(ListTabs&"!Critera2Range"),Criterion2))

    So, can anyone help me switch out the SUMIFS in these functions for another SUMIF/SUMPRODUCT/IF statement (Array formula or not)

    A minor addition to the INDIRECT references above:
    I've ensured there's no spaces within the sheet tab names within ListTabs. If you have spaces or other odd characters like & in your sheet tab names you need to enclose the reference with the ' and would have an INDIRECT formula like this:
    INDIRECT("'"&ListTabs&"!SumRange'")
    Last edited by ph1rst; 01-23-2013 at 06:27 AM. Reason: corrections and minor additions

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,961

    Re: SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

    Sounds like an interesting challenge ... But you've not provided anything to play with.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-06-2012
    Location
    Perth, Western Australia
    MS-Off Ver
    Mac 2011 & Windows 2010
    Posts
    5

    Re: SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

    Here's an example workbook, where the two tables in the Summary sheet use the formulae above...
    Attached Files Attached Files

+ 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