New Contributor

# Help with formula

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
3 Replies

# Re: Help with formula

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

# Re: Help with formula

@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

# Re: Help with formula

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