The single biggest requirement to make maximum use of these tricks is to have all of your data laid out in identical format across tabs.
This will sum all A1 cells on every worksheet in the workbook.
Put all of the worksheets in order and then add one empty sheet before the collection of worksheets you want to work with and one after the collection of worksheets you want to work with. Name the first tab START and the second tab END. This formula will sum all A1 cells that are inbetween these two sheets.
Both of these approaches will work with other common functions (ie =AVERAGE, =COUNT, =SUMIF, etc).
Excel File Available for Download: Download Template