Help with formula

[jamiesmith135]

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

Product	Qty	Date of Week	Week Commencing
30014000	193	22/09/2020	21/09/2020
30014000	768	24/09/2020	21/09/2020
30014000	400	16/10/2020	12/10/2020
30014000	480	30/10/2020	26/10/2020
30014000	240	06/11/2020	02/11/2020
30014000	200	20/11/2020	16/11/2020
30014000	330	27/11/2020	23/11/2020
30014000	360	01/01/2021	28/12/2020

[#2]

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

[#3]

@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 

[#4]

@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.