IMPORTING DATA FROM MULTIPLE SHEETS INTO ONE AND GETTING THEIR SUM

%3CLINGO-SUB%20id%3D%22lingo-sub-1622499%22%20slang%3D%22en-US%22%3EIMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622499%22%20slang%3D%22en-US%22%3E%3CP%3EHOW%20DO%20I%20CALCULATE%20A%20SUM%20OF%20DATA%20FROM%20MANY%20SHEETS%20INTO%20ONE%20SHEET%20IN%20THE%20SAME%20WORKSHEET%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1622499%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622550%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776664%22%20target%3D%22_blank%22%3E%40MIZPAH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20very%20depends%20on%20how%20your%20data%20is%20structured%20and%20what%20exactly%20you'd%20like%20to%20sum.%20Better%20to%20illustrate%20on%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622607%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622607%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bit%20is%20just%20weekly%20data%20(numbers)%20for%20different%20facilities.%20I%20want%20combine%20them%20into%20monthly%20data%26nbsp%3B%20for%20the%20consolidated%20report.it%20is%20in%20five%20tabs%2C%20different%20numbers%20for%20different%20age%20groups%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622738%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622738%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776664%22%20target%3D%22_blank%22%3E%40MIZPAH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20only%205%20sheets%20and%20structure%20is%20always%20the%20same%2C%20you%20may%20use%20SUMIFS%20repeated%20in%20formula%205%20times%20for%20every%20week.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622775%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622775%22%20slang%3D%22en-US%22%3ETHEN%20HOW%20DOES%20THE%20SUM%20OF%20ALL%20THE%20DATA%20GET%20ON%20ALL%20WEEKLY%20TABS%20GET%20ONTO%20THE%20MONTHLY%20TAB%20WHAT%20DO%20I%20PRESS%20FIRST%20BEFORE%20TYPING%20THE%20FORMULA%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622867%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622867%22%20slang%3D%22en-US%22%3EI%20HAVEN'T%20CREATED%20EXCEL%20TEMPLATES%20FOR%20QUITE%20A%20LONG%20TIME%2C%20THIS%20USED%20TO%20BE%20EASY%20BUT%20I%20AM%20STUCK%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622961%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776664%22%20target%3D%22_blank%22%3E%40MIZPAH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20help%20if%20you%20could%3A%3C%2FP%3E%3COL%3E%3CLI%3Epost%20a%20copy%20of%20the%20worksheets%20you're%20talking%20about%2C%20just%20making%20absolutely%20certain%20first%20that%20you've%20removed%20any%20names%20of%20real%20people%20and%20any%20other%20confidential%20information.%20As%20Sergei%20noted%20in%20his%20first%20response%2C%20so%20much%20depends%20on%20the%20structure%20of%20your%20data%2C%20whether%20it%20remains%20consistent%20from%20week%20to%20week%2C%20location%20to%20location%2C%20etc%2C%3C%2FLI%3E%3CLI%3Eturn%20off%20the%20caps%20lock%20key%20on%20your%20keyboard.%20(The%20use%20of%20all%20caps%20is%20generally%20regarded%20as%20the%20internet%20equivalent%20of%20shouting.)%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622989%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI%20eventually%20managed%20to%20maneuver%26nbsp%3B%20around%20and%20eventually%20got%20it.%20Thanks%20for%20the%20help.%20I%20wasn't%20about%20the%20caps%20thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622990%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thanks%20so%20much%20Sergei%2C%20I%20am%20sorted%2C%20I%20eventually%20managed%20to%20maneuver%20around%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1623187%22%20slang%3D%22en-US%22%3ERe%3A%20IMPORTING%20DATA%20FROM%20MULTIPLE%20SHEETS%20INTO%20ONE%20AND%20GETTING%20THEIR%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776664%22%20target%3D%22_blank%22%3E%40MIZPAH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20far%20so%20good%2C%20thanks%20you%20updated%20us.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

HOW DO I CALCULATE A SUM OF DATA FROM MANY SHEETS INTO ONE SHEET IN THE SAME WORKSHEET

9 Replies

@MIZPAH 

It very depends on how your data is structured and what exactly you'd like to sum. Better to illustrate on sample file.

@Sergei Baklan it is just weekly data (numbers) for different facilities. I want combine them into monthly data  for the consolidated report.it is in five tabs, different numbers for different age groups

@MIZPAH 

If that's only 5 sheets and structure is always the same, you may use SUMIFS repeated in formula 5 times for every week.

THEN HOW DOES THE SUM OF ALL THE DATA GET ON ALL WEEKLY TABS GET ONTO THE MONTHLY TAB WHAT DO I PRESS FIRST BEFORE TYPING THE FORMULA
I HAVEN'T CREATED EXCEL TEMPLATES FOR QUITE A LONG TIME, THIS USED TO BE EASY BUT I AM STUCK

@MIZPAH 

 

It would help if you could:

  1. post a copy of the worksheets you're talking about, just making absolutely certain first that you've removed any names of real people and any other confidential information. As Sergei noted in his first response, so much depends on the structure of your data, whether it remains consistent from week to week, location to location, etc,
  2. turn off the caps lock key on your keyboard. (The use of all caps is generally regarded as the internet equivalent of shouting.)

 

@mathetes I eventually managed to maneuver  around and eventually got it. Thanks for the help. I wasn't about the caps thanks 

@Sergei Baklan  Thanks so much Sergei, I am sorted, I eventually managed to maneuver around 

@MIZPAH 

So far so good, thanks you updated us.