Sum Identical Sheets - Faster Formula?
Results 1 to 8 of 8

Thread: Sum Identical Sheets - Faster Formula?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Posts
    921
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum Identical Sheets - Faster Formula?

    I am using this formula to sum between dates for a particular code number. The four sheets are identical in structure (with different table dimensions). I was wondering if there is a better option of doing this, as I believe this formula slows down my spreadsheet significantly.


    +SUMIFS(INDIRECT("BankFeed1[calc]"),INDIRECT("BankFeed1[DATE]"),">="&H$8,INDIRECT("BankFeed1[DATE]"),"<="&H$9,INDIRECT("BankFeed1[Code]"),"="&INDIRECT("BalTable[@Code]"))+SUMIFS(INDIRECT("BankFeed2[calc]"),INDIRECT("BankFeed2[DATE]"),">="&H$8,INDIRECT("BankFeed2[DATE]"),"<="&H$9,INDIRECT("BankFeed2[Code]"),"="&INDIRECT("BalTable[@Code]"))+SUMIFS(INDIRECT("BankFeed3[calc]"),INDIRECT("BankFeed3[DATE]"),">="&H$8,INDIRECT("BankFeed3[DATE]"),"<="&H$9,INDIRECT("BankFeed3[Code]"),"="&INDIRECT("BalTable[@Code]"))+SUMIFS(INDIRECT("BankFeed4[calc]"),INDIRECT("BankFeed4[DATE]"),">="&H$8,INDIRECT("BankFeed4[DATE]"),"<="&H$9,INDIRECT("BankFeed4[Code]"),"="&INDIRECT("BalTable[@Code]"))
    Last edited by neodjandre; Jun 1st, 2019 at 06:29 AM.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,824
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    For efficiency, I would suggest having a separate SUMIFS function for each sheet summing their respective data, and then simply using the SUM function to add those amounts. So, for example, if you place your SUMIFS function for each sheet in Sheet1!A2, Sheet2!A2, Sheet3!A2 and Sheet4!A2, then your sum formula would simply be...

    =SUM('Sheet1:Sheet4'!A2)

    Otherwise, you can shorten your formula as follows...

    =SUMPRODUCT(SUMIFS(INDIRECT("BankFeed"&ROW(INDIRECT("1:4"))&"[calc]"),INDIRECT("BankFeed"&ROW(INDIRECT("1:4"))&"[DATE]"),">="&H$8,INDIRECT("BankFeed"&ROW(INDIRECT("1:4"))&"[DATE]"),"<="&H$9,INDIRECT("BankFeed"&ROW(INDIRECT("1:4"))&"[Code]"),"="&INDIRECT("BalTable[@Code]")))

    Note, however, the formula is still somewhat inefficient. And, of course, it's a volatile formula.
    Last edited by Domenic; Jun 1st, 2019 at 08:57 AM.

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Posts
    921
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    Hmm.. I don't think I can go for the first option you are suggesting, but your suggested formula looks very neat. Do you think that would still be quicker than what I have? Or is it just cosmetically better? thank you

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,824
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    Probably the latter... But, actually, I'm not sure why you were using INDIRECT in your original formula. It looks like you could have simply avoided it in the first place...

    =SUMIFS(BankFeed1[calc],BankFeed1[DATE],">="&H$8,BankFeed1[DATE],"<="&H$9,BankFeed1[Code],"="&BalTable[@Code])+SUMIFS( etc . . .

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Posts
    921
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    I was thinking of the same, but the advantage of indirect is that if you delete the sheet BankFeed2, you don't get Ref errors afterwards in the formula...

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,824
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    You should still get an error, have you tried it?

  7. #7
    Board Regular
    Join Date
    Nov 2006
    Posts
    921
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    yes I have, no errors with indirect.. as the sheet name is stored as text

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,824
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum Identical Sheets - Faster Formula?

    That's strange, INDIRECT should return a #REF ! error, if the sheet or table referenced by the specified text doesn't exist.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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