Sep 25 2020 12:43 AM
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 |
Sep 25 2020 01:00 AM
@jamiesmith135 If you are an MS365 subscriber, perhaps the attached workbook contains a workable solution for you, using the new FILTER function.
Sep 25 2020 01:15 AM
@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
Sep 25 2020 01:36 AM
@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.