Help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1712025%22%20slang%3D%22en-US%22%3EHelp%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1712025%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20this%20is%20an%20easy%20answer%20so%20really%20appreciate%20any%20help.%3C%2FP%3E%3CP%3EMy%20dilemma%20is%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%202%20worksheets%20and%20worksheet%201%20needs%20to%20look%20up%20a%20value%20in%20worksheet%202%20and%20return%20the%20sum%20of%20the%20values%20for%20week%20commencing%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'm%20looking%20in%20worksheet%201%20value%20%3D%203001400%20then%20week%20commencing%20%3D%2021%2F09%2F2020%20and%20then%20want%20the%20sum%20of%20the%20rows%20that%20contain%20week%20commencing%20value%20in%20worksheet%202%20-%20So%20my%20answer%20is%20193%2B768%20%3D%20961%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22443px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3EProduct%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3EQty%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3EDate%20of%20Week%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3EWeek%20Commencing%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E193%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E22%2F09%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E21%2F09%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E768%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E24%2F09%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E21%2F09%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E400%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E16%2F10%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E12%2F10%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E480%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E30%2F10%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E26%2F10%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E240%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E06%2F11%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E02%2F11%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E200%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E20%2F11%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E16%2F11%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E330%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E27%2F11%2F2020%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E23%2F11%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%3E30014000%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E360%3C%2FTD%3E%3CTD%20width%3D%22119px%22%3E01%2F01%2F2021%3C%2FTD%3E%3CTD%20width%3D%22126px%22%3E28%2F12%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1712025%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1712091%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1712091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F808652%22%20target%3D%22_blank%22%3E%40jamiesmith135%3C%2FA%3E%26nbsp%3BIf%20you%20are%20an%20MS365%20subscriber%2C%20perhaps%20the%20attached%20workbook%20contains%20a%20workable%20solution%20for%20you%2C%20using%20the%20new%20FILTER%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there 

I'm sure this is an easy answer so really appreciate any help.

My dilemma is this

 

I have a 2 worksheets and worksheet 1 needs to look up a value in worksheet 2 and return the sum of the values for week commencing 

 

So I'm looking in worksheet 1 value = 3001400 then week commencing = 21/09/2020 and then want the sum of the rows that contain week commencing value in worksheet 2 - So my answer is 193+768 = 961

 

ProductQtyDate of WeekWeek Commencing
3001400019322/09/202021/09/2020
3001400076824/09/202021/09/2020
3001400040016/10/202012/10/2020
3001400048030/10/202026/10/2020
3001400024006/11/202002/11/2020
3001400020020/11/202016/11/2020
3001400033027/11/202023/11/2020
3001400036001/01/202128/12/2020
3 Replies
Highlighted

@jamiesmith135 If you are an MS365 subscriber, perhaps the attached workbook contains a workable solution for you, using the new FILTER function.

Highlighted

@Riny_van_Eekelen OMG what a genius!! The only thing I have now is ~CALC! if there is no values for that given week. Not a major issue but if there is a way to exclude this and leave a blank value it tidy's the spreadsheet

 

Many thanks 

 

Highlighted

@jamiesmith135 Oops, sorry about that. Should have included the "If empty" parameter of the FILTER function (the ",0" at the end). Try it this way.

=SUM(FILTER(Sheet2!B2:B9,(Sheet2!A2:A9=Sheet1!B1)*(Sheet2!D2:D9=Sheet1!B2),0))

 or you may wrap the original formula in an IFERROR statement.

=IFERROR(SUM(FILTER(Sheet2!B2:B9,(Sheet2!A2:A9=Sheet1!B1)*(Sheet2!D2:D9=Sheet1!B2))),"None found")

That way you get positive confirmation that there were no records meeting the set criteria.