SOLVED

Entering a sumif formula over multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1774334%22%20slang%3D%22en-US%22%3EEntering%20a%20sumif%20formula%20over%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774334%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20attempting%20to%20enter%20a%20sumif%20formula%20over%20multiple%20tabs%20by%20highlighting%20the%20tabs%20required.%20I%20have%20a%20seperate%20tabs%20for%20each%20week%20of%20the%20year%20and%20another%20containing%20the%20sales%20data.%20All%20of%20the%20criteria%20is%20within%20the%20same%20workbook%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF('Sales%20data'!B1%3ABL1%2C'Week%201'!H4%2C'Sales%20data'!B35%3ABL35)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20purpose%20is%20to%20pull%20through%20the%20relevant%20weekly%20figure%20from%20'sales%20data'%20into%20each%20separate%20weekly%20worksheet.%20B1%3ABL1%20relates%20to%20the%20week%20numbers%20and%20B35%3ABL35the%20sales%20figure.%20H4%20then%20relates%20to%20the%20week%20number%20in%20the%20individual%20weekly%20tabs.%20Every%20sheet%20is%20looking%20at%20'Week%201'%20instead%20of%20selecting%20the%20H4%20from%20the%20sheet%20with%20the%20formula%20in.%20I%20don't%20want%20to%20have%20to%20enter%20the%20formula%20individually%20on%20each%20of%20the%2052%20tabs!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1774334%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774407%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20sumif%20formula%20over%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830206%22%20target%3D%22_blank%22%3E%40Annlee107%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIF('Sales%20Data'!B1%3ABL1%2CH4%2C'Sales%20Data'!B35%3ABL35)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am attempting to enter a sumif formula over multiple tabs by highlighting the tabs required. I have a seperate tabs for each week of the year and another containing the sales data. All of the criteria is within the same workbook:

 

=SUMIF('Sales data'!B1:BL1,'Week 1'!H4,'Sales data'!B35:BL35)

 

The purpose is to pull through the relevant weekly figure from 'sales data' into each separate weekly worksheet. B1:BL1 relates to the week numbers and B35:BL35the sales figure. H4 then relates to the week number in the individual weekly tabs. Every sheet is looking at 'Week 1' instead of selecting the H4 from the sheet with the formula in. I don't want to have to enter the formula individually on each of the 52 tabs!

 

TIA

2 Replies
Highlighted
Best Response confirmed by Annlee107 (New Contributor)
Solution

@Annlee107 

Use

=SUMIF('Sales Data'!B1:BL1,H4,'Sales Data'!B35:BL35)
Highlighted