Need help with summing simple formula across sheets

[edfoxx36]

Hi All, first post here so I apologize if my the format of my questions isn't exactly right.

I'm trying to come up with a formula that will allow me to use a simple BEMDAS calculation across multiple sheets, and then sum the result.
I suppose a long-form version of this would look like;
[FORMULA]+(Sheet2!B2/Sheet2!B5)+((Sheet3!B2/Sheet3!B5)+(Sheet4!B2/Sheet4!B5)[/FORMULA and so on

i have many sheets, and my actual formula will require SUMIFS, so I dont really want to have to write out a crazy long formula.
Any recommendations/tips how I can trim this down?

Thanks,
EFOX

[pjwhitfield]

If your formula always uses the same range then Id use a UDF, the following will cycle through ALL sheets in the workbook, and get the totals of ALL ranges A1 to A3. You would just need to change the WorksheetFunction and add in any validation on the sheet names (for instance to not include a Sheet named TOTAL or whatever).

Please Login or Register  to view this content.

[MrShorty]

Are you familiar with 3D referencing? https://support.office.com/en-us/art...es_in_formulas

Here's how I would probably do something like this:

1) On each sheet, have a convenient cell that computes that sheets contribution to the sum. For your example, that would be =B2/B5
2) On the summary sheet, have a simple SUM() function with a 3D reference. =SUM(Sheet2:Sheet4!D1) (assuming you put the formula in (1) in D1 on each sheet).

If you are out to avoid a single crazy long formula, this easily avoids the crazy long formula in favor of a helper cell on each sheet.