Excel How To Sum Data Across Worksheets, Whether The Rows Are Lined Up Or Not - Episode 2572

Microsoft Excel Tutorial: Excel Summing Across Worksheets.

Some call it a 3D reference or a spearing formula. If your Excel worksheets are all perfectly lined up, you can use this formula to sum across worksheets in Excel.
But if your worksheets are not in the same sequence, then you can use TOCOL, SORT, UNIQUE, and SUMIFS to solve the problem.

Welcome to episode 2572 of our Excel tutorial series! In this video, we will be discussing how to sum data across multiple worksheets, even if the rows are not lined up. This can be a tricky task, but with the right techniques, it can be easily accomplished.

The first example we will be looking at assumes that all of the sheets are exactly lined up. This means that the data is in the same rows on each sheet. To begin, go to the "Total" worksheet and start where you want the first total to be. Type in "equal", "sum", and open parenthesis. Then, click on the first sheet and shift-click on the last sheet. This will automatically build the syntax for you in the formula bar. Finally, click on the correct cell and close the parenthesis. This will create a 3D reference, also known as a "spearing formula", that adds up the data across all the sheets.

However, things can get a bit more complicated when the sheets are not lined up. In this case, we can use a new function called "TOCOL" that respects 3D references. To do this, create two helper cells off to the right. In the first cell, use the TOCOL function to get the data from the sheets you want to sum. Then, in the second cell, use the same function to get the sales amounts. From there, you can use the SUMIFs function to get the totals for each product. This may seem like a lot, but it is a reliable way to sum data across non-aligned sheets.

As always, if you have any questions or comments, please feel free to leave them down below in the comments section. And if you found this video helpful, don't forget to like, subscribe, and ring the bell for notifications on future videos. Thank you for watching and happy Excel-ing!

This video answers these common search terms:
how to easy sum tabs in excel
how sum across multiple sheets in excel
how to calculate sum from other tabs in excel
how to do sum across two worksheets in excel
how to do sum of cells in multiple sheet in excel
how to get multi excel sheets to sum
how to get sum from multiple sheets in excel
how to sum across excel tabs
how to sum an entire workbook in excel
how to sum cells from different excel sheets
how to sum cells from different sheets excel
how to sum cells inexcel from multiple sheets
how to sum columns in excel from multiple tabs
how to sum different tabs in excel
how to sum forumla across multiple sheets in excel
how to sum from multiple excel sheets
how to sum from multiple sheets in excel
how to sum in excel across multiple sheets
how to sum in excel across tabs
how to sum in excel on different tabs
how to sum multiple pages in excel
how to sum multiple sheets excel
how to sum multiple tabs excel
how to sum multiple worksheets in excel
how to sum several sheets in excel
how to sum tabs in excel
how to summarize excel sheets on one
how to use sum function with 3d reference excel

Table of Contents
(0:00) Problem Statement: Exce; sum across worksheets
(0:10) If sheets are lined up, use a 3-D Reference in Excel
(1:09) When sheets are not aligned: TOCOL function
(2:00) SORT, UNIQUE, and SUMIFS
(2:37) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

[GeertDelmulle]

Hi Mr.Excel,
I see that you sum the values across the sheets, one cell at a time.
Here's a formula to do it in one cell provided "all the stars are perfectly aligned":
=BYROW(HSTACK(Sheet1:Sheet3!A1:A5), LAMBDA(x, SUM(x)))
PS: Some time ago I used VSTACK instead of TOCOL – works, too.

[ricos]

Great video Bill. It's for things like this that I'm glad power query exists! The number of errors I've seen over the years because of formulas that cross tabs (without using named references, or tables for the weirdos). 99% of the time they could be solved by having a single data source, with all formulae pointing to it, or separating data from reporting completely and have the data on the tabs "submitted" individually and then collated in PQ with a well designed pivot(s) giving them all the data they need with slicers acting as the Tab names. I can't remember the last time I used sheet name syntax in a formula for more than one sheet. I have a general rule that you only reference one other sheet from any given tab, and unless in extreme circumstances have no more than two or three tabs in a workbook (one if PQ is being used). But then I am a spreadsheet extremist.

[FoggyMooseBog]

Thank you SO MUCH. I'm stuck on a weekend working overtime hours and you just saved me a lot of time with sum over multiple sheets! MY HERO!

[jonbassinger-flores]

GREAT EXPLANATION! I just might get my homework done in time. :) Bookmarking for future reference!

[przejan]

Hi Bill. Great video - like always. About two years ago, you uploaded some riddle/test for Excel users. Data were based on the actual space exploration historical data. I have not seen any other "competitions" for your channel subscribers. I have been wondering if it was not popular among your audience and if it is the reason why you do not continue it.

[roywilson]

Great video, a real reminder of the way things used to be when working across multiple sheets with a few new twists using the new array formlae available now. The only thing I would say is that in this example (if it was allowed) I would go straight to power query, it having the added bonus that any data added to the individual sheets could easily be incorporated at every refresh of the final report and that it would make it so much easier to produce a Power Pivot table to summarise the data

[TimdeJong-em]

Tx Bill! Real timesaver. (used to manually total and getting so fed up), Grtz. Tim

[MadeForRivalsENT]

This is a great refresher video to using Excel. Thank you!

[nadermounir]

Great video Bill. Thank you for your hard work 👍

[leandratel]

👍🏼thanks for sharing, I have to replicate this to fully understand and I'm curious how the result is different to using CONSOLIDATE, a functionality I recently discovered