MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum All Sheets Faster

May 04, 2017 - by Bill Jelen

Sum All Sheets Faster

A faster way to build an Excel 3D formula, also known as a spearing formula. I usually type =SUM(Jan:Dec!H10), but you can do this using the mouse.

Watch Video

  • Sum a rectangular range on all worksheets
  • =SUM(
  • Click on Jan sheet
  • point to the range
  • Shift + Click on Dec sheet
  • press Enter
  • Or, type =SUM(Jan:Dec!B4)
  • Bonus trick: Adding Total Row & Column in one click of AutoSum

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode 2090
  • the 3d reference with two sets of colons
  • hey welcome back to the mr. cell net
  • cast I'm bill jealous question actually
  • came up in my seminar in Bloomington
  • Indiana and I usually talk about these
  • 3d references where we have all of these
  • different month names and then show how
  • to build a Spearing formula that will
  • add up cell b4 on each of the sheets
  • from January through December but what
  • came up today was no we just want one
  • form that's going to sum the whole thing
  • and yeah that is possible equal sum and
  • I want to come back here and click on
  • January choose all of the cells let's
  • get this out of the way choose all the
  • cells I want to sum from the January
  • sheet and then shift-click on to summer
  • and watch this formula right now right
  • now it's pointing at one rectangular
  • range on one sheet when i shift-click on
  • December I rewrites it into a
  • rectangular range on all of this sheets
  • from January through December I mean
  • don't you have to click the closing
  • parenthesis
  • just press ENTER and it goes and grabs
  • all of those numbers all the way across
  • cool cool formula like that with 2 : 2 :
  • in the sheet name and a colon in the
  • rectangular range we're pulling back now
  • what I usually show is just this little
  • formula here where we have to do equal
  • sum of January : December exclamation
  • point B for like that regular 3d
  • reference but it also works 3d reference
  • pointing to an entire range like that
  • hey let's just do one more bonus trick
  • here since this trick was so short if
  • you have to add totals across the bottom
  • and down the left down our hand side
  • most people come here hit the autosum
  • copy across and then here hit the
  • autosum and copy across check out this
  • awesome awesome way I just choose all
  • the numbers plus one extra column plus
  • one extra row then hit the auto sum and
  • adds the totals all the way around the
  • outside why did the number change
  • because these numbers are still ran
  • between but a cool couple of tricks
  • 3d references absolutely there in the
  • book PowerShell MrExcel the 2017 2017
  • edition click that I on the top right
  • hand corner to read more about the book
  • ok episode wrap up we're going to set my
  • rectangular range on all worksheets
  • start off by typing equals sum click on
  • the January sheet highlight the range
  • with the mouse and then shift-click on
  • the December sheet press Enter don't
  • even use a closing parenthesis or if
  • you're looking for just a single cell
  • equals some January : December
  • exclamation point and the cell that we
  • want a little bonus trick there at the
  • end adding the tilde row and column in
  • one click of auto sum well thanks for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2090.xlsm

Title Photo: sardenacarlo / Pixabay