Excel - How To Get Sum From Multiple Sheets In Excel Using 3D Reference - Episode 1984

Microsoft Excel Tutorial - Formula to sum across worksheets in Excel.
Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we'll be discussing how to create a summary sheet by summing all the data from multiple worksheets. If you're new here, make sure to subscribe to our channel and click the notification bell to stay updated on all our Excel tips and tricks.
So, let's say you have created monthly reports from January to December and now you need to create a summary report for the entire year. Instead of manually adding up the data from each month, there's a much faster way to do it. It's called a Spearing Formula or a 3D reference. I'll show you how to use this formula to quickly sum all the data from multiple worksheets.
First, type in =SUM( and then carefully click on the first sheet, in this case, January. Then, hold down the shift key and click on the last sheet, which is December. This will automatically add all the sheets in between. Next, click on the cell you want to add up and close the formula with a ). Press enter and voila! You have a formula that sums all the data from multiple sheets.
One thing to note is that the sheets need to be in the same shape for this formula to work. If you have sheet names with spaces, make sure to add apostrophes (') around the sheet names. However, if you use the trick I just showed you, you don't have to worry about this. Also, if you have quarterly sheets, you can use a wildcard (*) in the formula to sum them up. This is a trick shared by Bob Umlas, one of our contributors for the MrExcel book.
Speaking of the MrExcel book, it covers all 40 tips and tricks from our podcast series. It's a great reference guide and only costs $25. So, if you've been enjoying our free podcast series, we highly recommend purchasing the book to support us and have all the tips in one place.
In summary, the Spearing Formula or 3D reference is a fast and efficient way to sum data from multiple worksheets. Just remember to keep the sheets in the same shape and avoid adding new sheets in between. We hope you found this tip useful and stay tuned for more Excel tips and tricks from the MrExcel podcast. Thank you for watching and we'll see you in the next episode!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
Table of Contents
(00:00) Formula to Sum All Worksheets in Excel
(00:18) Creating a Summary Sheet
(00:37) Formula to Add Each Monthly Sheet
(01:03) 3D Reference - Easy Way to Enter
(01:51) Apostrophes around Sheet Names with Special Characters
(02:16) Bob Umlas: Only Add Quarters
(02:59) Episode Recap
(03:11) 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 #excelformula
Update from September 2023. What if your worksheets don't line up? See episode 2622: • Excel Sum Across Sheet...
This video answers these common search terms:
How To Make 3D Formula In Excel
How To Use A 3D Reference In An Excel Formula
How To Do Sum Of Cells In Multiple Sheet In Excel
How To Get Sum From Multiple Sheets In Excel
How To Sum Columns In Excel From Multiple Tabs
How To Sum Forumla Across Multiple Sheets In Excel
How To Do 3D Reference Excel
How Sum Across Multiple Sheets In Excel
How To Sum Different Tabs In Excel
How To Sum In Excel Across Multiple Sheets
How To Sum Multiple Sheets Excel
How To Create Formula Across Multiple Excel Worksheets
How To Use Formula Across Sheets In Excel
What Is A 3D Cell Reference Excel
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...

[@excelisfun]

Thanks for the amazing book and the great daily videos! I love it!!! : )

[@kapamagicman]

Wow...amazing . Have to try this!

[@sodethsun6149]

Thanks for the video it helps me a lot.

[@iffkitchen941]

Great video worked for me first attempt thank you

[@shivashankardshivu7821]

Use full thanks

[@Sal_A]

Nice trick to SUM worksheets with wildcard. Thanks!

[@excelisfun]

I agree - that is a very unusual and amazing * trick!!!

[@MrArafat0]

Hello Sir if i want to make all sheet a summery on a Sheet all month total amount separately. how can i do it?

[@serarakavono9255]

Is it possible to still carry out this summation successfully if the table formats are not consistent throughout the sheets in the workbook ?????

[@MrXL]

The method shown in this video requires the sheets to be set up the same.

[@Explore_Your_Choice]

Thanks for your prompt response but the query is not sufficed. Look I have one distributor under it i have a three different months and then by per month i have two columns one for retail and second for wholesale. now look column, shape i.e., 'Retail'Wholesale''Retail'Wholesale''Retail'Wholesale' this is appearing month wise like "May"June"July" under one distributor now these six columns are being repeated 54 times left to right. In the right end I need sum of 54 distributors for the month of May "Retail" only as grand total. and I don't want to apply this method like =E10+K10+Q10. what should I do to minimize this practice?

[@SuperRajeshnair]

What if the values cell A values are shuffled in each sheet, how to find the matching text and add values?

[@MrXL]

Here is a video on how to combine everything in memory. kzbin.info/www/bejne/jHiZdnejZ7KUb8k. You could add steps to Group By column A and Sun everything else.

[@marshalleastin2003]

I have a question that I haven't been able to find. I work in a call center and I keep track of my own calls on an excell sheet so I can make sure I am meeting my goals. Have one file for the whole month and I have a sheet tab for each day I work. Right now what I do is set up for day one of the month (with a blank template that I have) I zero it out. What I keep track of is the results of each call as well as the total number of calls. I have three categories for results of a call that I have my daily totals on each sheet. Then the next day I copy and move to end the previous sheet and zero it out and fill it in for that day. I know how to get a monthly total at the end of the month. But what I would like to do is have it set up so that I can always see my month to date. Without having to reset it up every time and just copy it over. Is there a way to do that?

[@MrXL]

The whole trick to this is to put your totals at the TOP of the worksheet. Maybe you have date in A1 and then in row 3, you could have something like =COUNT(A5:A200) and =SUM(J5:J200). The reason you put the totals at the top is so you know the totals are always going to be in row 3. If you do the natural thing and put the totals at the bottom, then some days they will be in row 12 and other days they will be in row 82, and then it is a pain to total them. The next thing that I want you to do is to insert a sheet to the left of your first sheet of the month. Rename that sheet to be Start. Put a sheet to the right of your last sheet of the month and call it End. Then, put a sheet to the right of End. Call that sheet Totals. The formula on Totals is a *spearing* formula in that it spears through 33 sheets, grabbing J3 from each sheet: =SUM(Start:End!J3). I have done this in half a dozen videos. Here is one: kzbin.info/www/bejne/Z5fXfppqbtKfe9E

[@optimisationbyamy]

Any idea if there is an equivalent for Google Sheets?

[@MrXL]

No idea. Check Leila Gharani's channel. She has some Google Sheets content.

[@Explore_Your_Choice]

Dear Mr. Excel I am your subscriber and found very satisfactory solutions by watching your uploaded videos. I am in trouble reference podcast 1984 the method I want to apply is same but I need sum on the same sheet I mean I have all my data left to right in one sheet and in the right end I want the sum results. I have applied your given solution but failed. Please help me to fix it.

[@rosiecaballero2158]

okay, so I followed the instructions and created my formula in excel. Now the problem is that if I delete the numbers my formula disappears. I am not a computer person by any means so any help is appreciated.

[@MrXL]

Hi Rosie After you have the formula working, but before you delete the numbers, you want to Copy the formula cells and Paste Values. Here is a 60-second demo: kzbin.info/www/bejne/lYu4npScbr-agsk

[@rebazosman1240]

hello sir if we have different cell in different sheet how can i sum?

[@MrXL]

Here is a video showing how to combine data from worksheets where the rows do not line up: kzbin.info/www/bejne/i4CUYnSpmpaSj9U

[@w.tibbsclemens636]

You sound like an airline pilot

[@1odham]

This is assuming that all of the totals to be summed up are in the same cell. What if the cells are different?

[@MrXL]

It becomes far more difficult when they aren't lined up. Do you have the headings in the same row on each worksheet? You could combine all the sheets with VBA using this method: kzbin.info/www/bejne/pGq6lINnbdyLfKM But the better way is to virtually combine all the sheets in memory using the Get & Transform tools on the Data tab. This video walks you through it: kzbin.info/www/bejne/jHiZdnejZ7KUb8k Once you get a virtual grid with all of the data across all worksheets, you can use the Power Query editor to Group by a field and Sum all records across all worksheets. This is a complex set of steps, but once you get it set up, it would work great. If you want to share details about your data set, send me an e-mail. Click on the "About" link in my profile and you can get my e-mail address.

[@1odham]

@@MrXL what I’m doing is tracking the days output per hour per day per month on one tab. All of the days are the same. But, using overages we find on the floor, that would be in row 27. Then each day would have a different cell, such as T, then TA, QR, etc. I would like an easy way other than =SUM( and then having to click each cell for the formula.

[@MrXL]

@@1odham Is there some heading in T, TA, QR that tells you this is an item that you would want to include? For example, does row 4 say "Daily Total" for the columns that should be included? If so, you could do =SUMIFS(Sheet1!A27:XFD27,Sheet1!A4:XFD4,"Daily Total") This would add up the relevant cells on Sheet1. But it sounds like you have multiple sheets. I would add a new Summary worksheet. In A3, put a list of the worksheets. Maybe the worksheet names are 'Week One' in A3 'Week Two' in A4 'Week Three' in A5 'Week Four' in A6 If Summary!A3 says "Week One" then you could have a cell in Summary!B3 that would do the same SUMIFS: =SUMIFS(INDIRECT(A3&"!A27:XFD27"),INDIRECT(A3&"!A4:XFD4"),"Daily Total") The INDIRECT function takes something that looks like a valid cell address 'Week One'!A27:XFD27 and converts it to a cell address. The advantage here is that you can drag your B3 formula down to all of the other rows and it would point to the next sheet name. It is tough to explain this in hypothetical terms... without seeing the actual worksheet.

[@1odham]

@@MrXL yes. But there are several daily totals. Could I send you a screen shot? Or the file?

[@1odham]

Because I will have to do this with all twelve months.

[@PAMSAAM]

This made absolutely no sense!