google sheets week/year array formula

Question

I have sheet1 that looks like this:

and sheet2 looks like this:

ColA: =UNIQUE(ARRAY_CONSTRAIN({"Week/Year";arrayformula(IF(ISBLANK(Sheet1!A$2:A);"";CONCAT(CONCAT(WEEKNUM(Sheet1!A$2:A;2);"/");TEXT(Sheet1!A$2:A;"YY"))))};COUNTA(Sheet1!A$2:A);1))

ColB: =SUM(FILTER(Sheet1!B$2:B; ARRAYFORMULA(WEEKNUM(Sheet1!A$2:A;2)=index(SPLIT(A2;"/");0;1)))) <--I need help here

A chart is built on the basis of sheet2:

I need to get the sum of values from column B if the week number and year are the same as column A (Column A is date format dd.mm.yyyy). I managed to filter the amount by week number, but I can't add a second check for the year. Please help me, I've already tried everything.

Sheet example: https://docs.google.com/spreadsheets/d/1XjJLOC5NHZwKirxbxZQzPAzpU8nCvhNs2_M323rZf3U/edit?usp=sharing

Answer 1

Is this what you need (cell A1)? Delete the values in column B.

=arrayformula(query(query({Sheet1!A:B\if(Sheet1!A:A<>"";{weeknum(Sheet1!A:A;2)&"/"&right(year(Sheet1!A:A);2)\year(Sheet1!A:A)&"-"&text(weeknum(Sheet1!A:A;2);"00")};)};"select Col4,Col3,sum(Col2) where Col3 is not null group by Col4,Col3 label Col3 'Date', sum(Col2) 'Value' ";1);"select Col2,Col3";1))

Notes:

I use an array {} to get the following 4 columns:

Col1 and Col2: Sheet1!A:B

Col3: weeknum(Sheet1!A:A;2)&"/"&right(year(Sheet1!A:A);2) (eg. n/yy)

Col4: year(Sheet1!A:A)&"-"&text(weeknum(Sheet1!A:A;2);"00") (eg. yyyy-nn)

Col3 and Col4 only return a value whilst there are values down column A: if(Sheet1!A:A<>"";

Col3 is the weeknum and year displayed in your graph.

Col4 is the same but formatted differently for the sort.

The first query gets Col4,Col3 and the sum of Col2, then the second query gets Col3 and sum(Col2).