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

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

Sorry for inconvenience.

I have updated the previous post the attached the revised file.

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

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.

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.

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.

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

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

Or INDIRECT RC Style

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

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

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.

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

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

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

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1