# Sum Identical Sheets - Faster Formula?

#### neodjandre

##### Well-known Member
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:

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

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

#### neodjandre

##### Well-known Member
yes I have, no errors with indirect.. as the sheet name is stored as text

#### Domenic

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

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