Sum the same cell across multiple sheets in Excel | Excel time saving tip

[@ReadySteadyDESTROY]

Watched multiple videos and read a lot of articles and this is the only one that worked immediately. Thank you so much!

[@ExcelOffTheGrid]

You're welcome!

[@bejai1962]

My job recently asked for my monthly totals, which were always done in a Word doc, to be sent as one file for comparison each month. I figured there had to be an simpler way in Excel. This is just what I needed. You explanation is straight to the point and easy to follow! Only 2 minutes in and I was able to set up my sheets. Thx a much!!!

[@ExcelOffTheGrid]

Woop woop 💥 Great news Bernadette. Thanks for letting me know. 😀

[@jeanlane4978]

Great explanation. It took me ages before I came across your tutorial, getting nowhere on so many other sites. You made it so simple.

[@ExcelOffTheGrid]

You're welcome! Glad it helped.

[@mohamedfahmy1735]

Loved the idea of inserting "Start" and "End" sheets. Thank you so much.

[@ExcelOffTheGrid]

Glad it was helpful!

[@Jill_Liu]

great trick. it is so flexible to include or exclude sheets that involve the operation.

[@rongillaspy6479]

Great help! Thank you so much!!

[@orboakin8074]

Thank you fir this video. Made what the college professor couldn't properly explain to me much easier. Thanks!

[@ExcelOffTheGrid]

Lol! Glad I could help. 😀

[@wayneedmondson1065]

Hi Mark. Great video! I remember when I first learned about this feature.. saved so much time.. but as you discussed, you must be very careful to maintain a consistent structure for each sheet. That said, it can be a very useful technique to quickly create a summary when you have the right circumstances and format. Thanks for sharing :)) Thumbs up!!

[@ExcelOffTheGrid]

Thanks Wayne. I agree. It's great in the right circumstances, but there are dangers if people don't know what they are doing (I'm sure I've made all those errors at some point in the past 😊)

[@stepheneeangilena5474]

Thank you ! Thank you! Thank you! Your video was easy to follow and got the job done!

[@ExcelOffTheGrid]

Great to hear!

[@lpanades]

Simply very very helpful advices about layout and nice explanation about Excel structural layout. Essential knowledge about Excel. Thanks a lot.

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@ecomstories]

OMG this saved me so much time. Thank you.

[@ExcelOffTheGrid]

You're welcome!

[@chriscarr5229]

Thank you! Such a clear explanation.

[@ExcelOffTheGrid]

Thanks Chris.

[@user-zz4bx2eq1d]

This was an amazing help -- THANK YOU :)

[@ExcelOffTheGrid]

You’re welcome. 👍

[@mariamaravilla1977]

Wow! So helpful. Thank you very much!

[@ExcelOffTheGrid]

You're very welcome!

[@doughalper]

So well explained! Thank you!

[@ExcelOffTheGrid]

Glad it was helpful! 😁

[@OakleyTurvey]

Wooo! 3 Dimensional formulae. Agree with Wayne below which is why I use PQ for this now.

[@ExcelOffTheGrid]

Yes, I agree. PQ is definitely a more robust solution for handling data.

[@stevenmcculloch5282]

Adding Start and End helped a lot since my sheets are date named. Thank you

[@ExcelOffTheGrid]

Yes, ‘Start’ and ‘End’ definitely help to simplify things. 👍

[@TPPembabulous]

Exactly what I was looking for. :)

[@ExcelOffTheGrid]

Glad I could help!

[@mrs_varon]

Thank you! :)

[@jankovicsr]

Thank you!

[@KeyserTheRedBeard]

phenomenal content Excel Off The Grid. I killed that thumbs up on your video. Always keep up the excellent work.

[@ExcelOffTheGrid]

Lol!! Thank you. Don’t use up all those thumbs up, you should save same for my other videos 👍

[@KeyserTheRedBeard]

@@ExcelOffTheGrid no problem at all. always happy to support my fellow content creators. I am looking forward to improving on my own current video format I have uploaded and, in the future, trying to create content as good as yours in the long term.............. ill save some haahah

[@SuperBarnab]

Excellent video!

[@ExcelOffTheGrid]

Thank you very much!

[@dubaispectacle]

Thank you helpful...

[@ExcelOffTheGrid]

Thanks, glad I could help.

[@vinayakpotadar4093]

Super sir

[@dinahany233]

u can use consolidate icon in Data Ribbon

[@rebazosman1240]

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

[@erickdias5007]

brilliant Job, very useful and well explained. i've a question here, what if we have tabs which is not subsequent and have different names with a key word I.E Budget Jan 22 / Budget feb 22.. etc.....but this tab is not beside each other as we would have tabs not to be picked? can it be done by the key word Budget?

[@ExcelOffTheGrid]

You would need to use Power Query for that scenario. Check out my Power Query series here: exceloffthegrid.com/power-query-introduction/

[@bato-wm5gl]

I am trying =SUM(Load1:Load2!B3) and it's not working for me. Why? 😭

[@ExcelOffTheGrid]

You’ve missed the single quotes around the sheet names.

[@RCG025]

Great🎉 Ive been finding tips on how to get sum across multiple sheets.Yikes thank you this is great I have totally learned a lot. But can I ask a question ? for instances like getting the max from Jan to March could I use this formula by just typing equal sign and then max word?

[@ExcelOffTheGrid]

Yes, you can just type =MAX( into a cell to start the formula. The challenge is getting the right syntax for referencing the cell across all the sheets.

[@serarakavono9255]

What about for instances where the cell addresses are inconsistent ? is it possible to assign a unique key field that can be used as cell references for summation across the worksheets ?

[@ExcelOffTheGrid]

If the cell address aren't consistent, you'll need to use Power Query to combine the data.

[@aaronpecotte8845]

great easy to understand video. I'm a novice and I am trying to add previous sheet total throughout workbook. Keeping a month to month annual total. any tips?

[@ExcelOffTheGrid]

🤔 Hmmm…. Interesting scenario. I think that will probably require the INDIRECT function to call a different range of sheets for each running total.

[@Endlessnight500]

I had to do something similar, but week to week totals adding to year to date. I used two workbooks. In my year to date column I used something like =sum(start:end!B2,B2) and just put the end page right before my current week. That way it added all of the cells plus the current week, then I copied the current worksheet data with the year to date info into another workbook so each week would be current for that week only.

[@chrisoli7751]

I'm curious. Can I use a master sheet with data and link that data to go into multiple sheets? I run a 24 team baseball league and use excel for all data. Each team has a tab for it's players and salary. I wanted to use a master sheet with all the players and the players salary and link that to all the 24 team tabs. So if a trade occurs I could just switch the teams in the master sheet and it will then automatically swap those players in the individual team tabs

[@ExcelOffTheGrid]

Based on what you have described, I don’t think this method will do what you want.

[@GaneshSonwane-ek8dg]

can this be used for Sumif where I have references from multiple sheets?

[@ExcelOffTheGrid]

No it won't work with SUMIFS. You will need to load the sheets into Power Query; Close & Load to a Table. Then you will be able to use SUMIFS.

[@user-jp8dk1mu6v]

Hi .... nice information. But, I am getting this error message "Error Unknown range name: 'APRIL23'." Here, APRIL23 is name of the worksheet. I want to sum the monthly wise attendance of the students from APRIL-23 to MARCH-24. Will you please help me.

[@richardericson1034]

so how to sum different cell across multiple sheets by searching with the part number?

[@ExcelOffTheGrid]

You can’t using this method. You would need to: - Power Query into a single data source - Load as Table - Perform SUMIFS on the Table.

[@1988kene]

did not work... i had two tabs - help.....please!!!

[@ppyong4055]

can someone tell me how to deal with hidden sheet? I want to sum with same cell and different sheet like this but I have lots of hidden sheet. It would take many times if I keep repositioning them to analyze the data.

[@ExcelOffTheGrid]

Unhide all the sheets, and move them outside the Start and End sheets. It's all about your management of the workbook.

[@sushantsaxena5333]

If the sheets are in different format then how we can sum?

[@ExcelOffTheGrid]

In that scenario, Power Query might be a better option.

[@cocotower]

Why can't we just specify the same cell Once in the case all of the sheets have the same layout which is probably the case. I found it painful to have to enter cell D2 over and over 12 times for the year. The =SUM(Sheet1:Sheet12!D2) formula generates an error even though my sheet names are correct and the D2 cell itself contains a formula in each sheet.

[@ExcelOffTheGrid]

You’re missing the single quote characters around the sheet names.

[@ritikamaheshwari7983]

The formula gets messed up after inserting new rows

[@ExcelOffTheGrid]

Yes, if the sheets don’t stay in sync then can’t work.

[@RunwayCats]

this only works if your sheet name doesnt have spaces...

[@RunwayCats]

=SUM(Week 13:week 49!I11) doesnt work for me :(

[@ExcelOffTheGrid]

You haven’t referenced the sheets properly. If there is a space, sheet names need to be enclosed in single quotes. =SUM(‘Week 13:Week 49’!I11)

[@stevenmcculloch5282]

did you try the START & END tabs, that worked for me

[@bato-wm5gl]

@@RunwayCats did u find solution? Having the same issue

[@DIAVOLOO]

Still dont work for me, made 1 sheet named start, one named end, enter the formula =sum(start:end! D28), and get the message the zone named start not found. Then i tried sum('start:end'! d28), and get error, the sheet named start:end dont exist.. I dont understand why..