Get The Completed Workbook

If you’ve ever had to sum up items across many different sheets, then you know it can be a real pain when there are a lot of sheets. This trick will make it super easy.

 

Step-001-How-To-Sum-Across-Multiple-Sheet-In-A-Workbook-The-Easy-Way-1024x598 How To Sum Across Multiple Sheets In A Workbook

 

In this example, we have a table of sales figures each in a separate tab named Jan through Dec. Each sheet is the same format with the table in the same position within each sheet.

 

Step-002-How-To-Sum-Across-Multiple-Sheet-In-A-Workbook-The-Easy-Way-1024x598 How To Sum Across Multiple Sheets In A Workbook

 

If you wanted to create a Total sheet and have a table in it that sums up each of the tables in the Jan to Dec sheets, then you could use this formula and copy it across the whole table.

=Jan!C3+Feb!C3+Mar!C3+Apr!C3+May!C3+Jun!C3+Jul!C3+Aug!C3+Sep!C3+Oct!C3+Nov!C3+Dec!C3

Creating this formula isn’t very efficient though, as it requires selecting the Jan sheet, then selecting the cell C3, then typing a +, then selecting the Feb sheet etc… Going through 12 sheets in all. There is a better way!

 

Step-003-How-To-Sum-Across-Multiple-Sheet-In-A-Workbook-The-Easy-Way-1024x598 How To Sum Across Multiple Sheets In A Workbook

 

Add the sum formula into the total table.

  1. Type out the start of your sum formula =SUM(.
  2. Left click on the Jan sheet with the mouse.
  3. Hold Shift key and left click on the Dec sheet. Now select the cell C3 in the Dec sheet. Add a closing bracket to the formula and press Enter. Your sum formula should now look like this.
    =SUM(Jan:Dec!C3)

 

Step-004-How-To-Sum-Across-Multiple-Sheet-In-A-Workbook-The-Easy-Way-1024x597 How To Sum Across Multiple Sheets In A Workbook

 

The formula will sum up C3 across each of the sheets Jan to Dec. You can also use this technique with other formulas like COUNT or AVERAGE etc… An easier way over cycling through each sheet individually.