Home

totaling cells from multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-319740%22%20slang%3D%22en-US%22%3Etotaling%20cells%20from%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319740%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20in%20no%20way%2C%20shape%20or%20form%20an%20Excel%20expert%2C%20so%20I%20will%20probably%20ask%20more%20questions%20than%20I%20would%20ever%20be%20able%20to%20answer.%20Sorry%20if%20that's%20annoying!%3C%2FP%3E%3CP%3EHere's%20the%20first.%26nbsp%3B%20I%20have%20a%20work%20book%20with%20multiple%20tabs.%26nbsp%3B%20I'd%20like%20to%20take%20information%20from%20a%20cell%20on%20the%20previous%20tab%20and%20add%20it%20to%20information%20on%20the%20next%20tab%20so%20that%20there's%20a%20total%20from%20the%20two%20tabs.%26nbsp%3B%20And%20then%20on%20the%203rd%20tab%20I'd%20like%20to%20take%20a%20number%20from%20a%20cell%20and%20add%20it%20to%20the%20total%20from%20tab%20%232%2C%20so%20that%20on%20each%20tab%20I%20get%20a%20running%20total.%26nbsp%3B%20I%20tried%20writing%20the%20formula%20as%26nbsp%3B%3DSUM(sheet5!O43%2Csheet6!O34)%2C%20but%20I%20get%20an%20error%20message%20that%20says%20that%20there's%20an%20invalid%20cell%20reference.%26nbsp%3B%20Is%20this%20because%20my%20tabs%20have%20different%20names%3F%20I%20mean%2C%20they're%20actually%20named%20things%20like%202019%20Jan%20Paid%2C%20not%20sheet5.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EHeather%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-319740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319806%22%20slang%3D%22en-US%22%3ERe%3A%20totaling%20cells%20from%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319806%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Heather%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20don't%20need%20%22%2B%22%20in%20the%20SUM.%20In%20general%3C%2FP%3E%0A%3CPRE%3E%3Da%2Bb%2Bc%0A%0Aand%0A%0A%3DSUM(a%2Cb%2Cc)%3C%2FPRE%3E%0A%3CP%3Ereturn%20exactly%20the%20same%20if%20all%20of%20your%20a%2Cb%2Cc%20are%20numbers.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPractically%20the%20only%20difference%20is%20what%20SUM%20ignores%20texts%2C%20for%20example%3C%2FP%3E%0A%3CPRE%3E%3D%22some%22%20%2B%2010%20%20%20%20%20%20%20%20%20%20%20%20%20%26lt%3B%3D%20returns%20error%0A%3DSUM(%22some%22%2C10)%20%20%20%20%20%20%20%20%20%20%26lt%3B%3D%20returns%2010%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319779%22%20slang%3D%22en-US%22%3ERe%3A%20totaling%20cells%20from%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3EThanks%20for%20trying%20to%20help%20with%20this.%26nbsp%3B%20The%20formula%20now%20reads%26nbsp%3B%3DSUM('%5B2018DecPaid%5D2018%20Dec%20Paid'!O37%2B%2C'%5B2019JanPaid%5D2019%20Jan%20Paid'!O34%2B%2C'%5B2019FebPaid%5D2019%20Feb%20Paid'!O34)%26nbsp%3B%20but%20it%20is%20not%20picking%20up%20the%20data%20from%20the%20second%20sheet%20or%20the%203rd%20sheet%20and%20adding%20them%20in%20to%20create%20the%20running%20total.%26nbsp%3B%20Any%20more%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EHeather%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319763%22%20slang%3D%22en-US%22%3ERe%3A%20totaling%20cells%20from%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319763%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20exactly%2C%20references%20on%20another%20sheets%20looks%20like%3C%2FP%3E%0A%3CPRE%3E%3D'2019%20Jan%20Paid'!A1%2B'2019%20Feb%20Paid'!A1%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Heather_DeBey
New Contributor

Hi,

I am in no way, shape or form an Excel expert, so I will probably ask more questions than I would ever be able to answer. Sorry if that's annoying!

Here's the first.  I have a work book with multiple tabs.  I'd like to take information from a cell on the previous tab and add it to information on the next tab so that there's a total from the two tabs.  And then on the 3rd tab I'd like to take a number from a cell and add it to the total from tab #2, so that on each tab I get a running total.  I tried writing the formula as =SUM(sheet5!O43,sheet6!O34), but I get an error message that says that there's an invalid cell reference.  Is this because my tabs have different names? I mean, they're actually named things like 2019 Jan Paid, not sheet5.  

 

Thanks,

Heather

3 Replies

Yes, exactly, references on another sheets looks like

='2019 Jan Paid'!A1+'2019 Feb Paid'!A1

Hi Sergei,

Thanks for trying to help with this.  The formula now reads =SUM('[2018DecPaid]2018 Dec Paid'!O37+,'[2019JanPaid]2019 Jan Paid'!O34+,'[2019FebPaid]2019 Feb Paid'!O34)  but it is not picking up the data from the second sheet or the 3rd sheet and adding them in to create the running total.  Any more ideas?

 

Thanks,

Heather

Hi Heather,

 

You don't need "+" in the SUM. In general

=a+b+c

and

=SUM(a,b,c)

return exactly the same if all of your a,b,c are numbers.

 

Practically the only difference is what SUM ignores texts, for example

="some" + 10             <= returns error
=SUM("some",10)          <= returns 10

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies