Sum Identical Sheets - Faster Formula?

[neodjandre]

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

"),"="&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]"))

 

[Domenic]

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"))&"

"),"="&INDIRECT("BalTable[@Code]")))

Note, however, the formula is still somewhat inefficient.  And, of course, it's a volatile formula.

 

[neodjandre]

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]

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

,"="&BalTable[@Code])+SUMIFS( etc . . .

 

[neodjandre]

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...

 

[Domenic]

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

 

[neodjandre]

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

 

[Domenic]

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