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


 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.