Help with formula

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

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

@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 

 

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