# Formula required for calculating sum from many worksheets.

1. ## Formula required for calculating sum from many worksheets.

Dear Team,

PFA

I would to calculate the Sum from various sheets.
I have a formula mentioned in C3 of Summary sheet. (It was give by forum only)

But its very time consuming if I copy paste that formula in approx. 250 columns since formula need to be modified everytime.

Is it possible to simply this formula since the data base is huge and its time consuming.?  Register To Reply

2. ## Re: Formula required for calculating sum from many worksheets.  Register To Reply

3. ## Re: Formula required for calculating sum from many worksheets.

Sorry for inconvenience.

I have updated the previous post the attached the revised file.  Register To Reply

4. ## Re: Formula required for calculating sum from many worksheets.

Please try this in C3 of 'Summary' filled down and across.
Formula:  `Please Login or Register  to view this content.`  Register To Reply

5. ## Re: Formula required for calculating sum from many worksheets.

This may not work since the row sequence is not same in all the sheets.  Register To Reply

6. ## Re: Formula required for calculating sum from many worksheets.

lalaarif1

What that says is that the upload provided is not truly representative of what you are really working with.

In the meantime we often see the practice of capturing data in sheets and then trying to summarize that data.
That is backwards to efficient workbook design and will often result in excessive calls to the volatile INDIRECT function.
If you are not familiar with volatility and its importance this link may be of help.

http://www.decisionmodels.com/calcsecretsi.htm

A far better approach would be to gather all data in one sheet as a 2D flat database. This is advantageous because:
1. It avoids excessive calls to INDIRECT as mentioned above.
2. You have more options for summarization besides monthly:
• filters
• formula based summaries
• it opens you to the wonderful world of Pivot tables.  Register To Reply

7. ## Re: Formula required for calculating sum from many worksheets.

Hi,
The attachment was for sample only.

In C3, I could have used SUMIF() and copy pasted the same in other column.
But it takes time in calculating the values.

Each sheet has approx 20000 row and around 50 columns.
Sequence of row is not same. but columns heading sequence are same.

in C3, =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!\$b:\$b"),\$B3,INDIRECT("'"&SheetList&"'!c:c"))) works perfectly fine.
But only issue is I can not copy the same formula in D3 onwards.  Register To Reply

8. ## Re: Formula required for calculating sum from many worksheets.

Maybe,

In "Summary" sheet C3, formula copied across and down :

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!\$B:\$B"),\$B3,INDIRECT("'"&SheetList&"'!"&CHAR(66+COLUMNS(\$A:A))&1)))

Regards
Bosco  Register To Reply

9. ## Re: Formula required for calculating sum from many worksheets.

Or INDIRECT RC Style

C3
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2",),\$B3,INDIRECT("'"&SheetList&"'!C",)))  Register To Reply

10. ## Re: Formula required for calculating sum from many worksheets.

In C3 of summary sheet then dragged across.

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!\$b:\$b"),\$B3,OFFSET(INDIRECT("'"&SheetList&"'!C:C"),0,COLUMNS(\$C3:C3)-1)))  Register To Reply

11. ## Re: Formula required for calculating sum from many worksheets.

Thanks a lot for helping with formula.

PFA the revised file.

I am facing 1 issue.
When I inserted few rows and columns in the working data, the formula fails.  Register To Reply

12. ## Re: Formula required for calculating sum from many worksheets.

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C3",),\$C7,INDIRECT("'"&SheetList&"'!C",)))  Register To Reply

13. ## Re: Formula required for calculating sum from many worksheets.

Hello Bo_Ry,
You made my day. It works perfectly fine.  Register To Reply

14. ## Re: Formula required for calculating sum from many worksheets.  Register To Reply