Sum Identical Sheets - Faster Formula?

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
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:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,205
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:

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
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 :)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,205
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 . . .
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
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... :)
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
yes I have, no errors with indirect.. as the sheet name is stored as text
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,205
That's strange, INDIRECT should return a #REF ! error, if the sheet or table referenced by the specified text doesn't exist.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,101
Messages
5,484,678
Members
407,462
Latest member
TapetalRegent

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top