Sum the Same Cell Across Multiple Excel Sheets (3D Reference Trick)

If you were creating an Excel model with several worksheet tabs to describe each unique scenario and you wanted to aggregate a certain item, there is a short way to do so without having to open up the =SUM() formula and click through each tab to reference all the instances of the item. This hack works as long as you maintained model integrity by ensuring that the columns and rows represented the same information across tabs - for instance Column E in all workbooks contained Quantity information, Column J in all tabs contained Price information, and so on. In the sheet you want to show the aggregated information, simply type:

=SUM('*'!A15)

What this formula does is use the asterisk wildcard (*) to pull all instances where any name appears before an exclamation mark - this is the way MS Excel denotes sheet names - and sum up all values occurring in cell reference A15 across all the worksheets. If you wanted to restrict the sum to certain worksheets, you could use:

=SUM(Sheet1:Sheet4!D7)

Asides from =SUM(), other mathematical formulas like =AVERAGE(), =COUNT(), =MAX(), =MIN() could be used in a similar fashion.