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

1. ## 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]"))  Reply With Quote

2. ## 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.  Reply With Quote

3. ## 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   Reply With Quote

4. ## 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 . . .  Reply With Quote

5. ## 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...   Reply With Quote

6. ## Re: Sum Identical Sheets - Faster Formula?

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

7. ## Re: Sum Identical Sheets - Faster Formula?

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

8. ## 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.  Reply With Quote

## User Tag List

#### Tags for this Thread

formula, identical, sheets, sum, wondering #### Posting Permissions

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