How to properly implement sumifs in excel?-CodePudding

I have a table as follows which has year month combinations in rows and columns in sheet "grid".

year    year    2019    2019    2019    2019    2019    2019    2019    2019    2019    2019    2019    2019    2020    2020
year    year-mm 2019-1  2019-2  2019-3  2019-4  2019-5  2019-6  2019-7  2019-8  2019-9  2019-10 2019-11 2019-12 2020-1  2020-2
2019    2019-1  3   4   0   0   0   0   0   0   0   0   0   0   0   0
2019    2019-2  4   0   0   0   0   0   0   0   0   0   0   0   0   0
2019    2019-3  0   0   0   0   0   0   0   0   0   0   0   0   0   0
2019    2019-4  0   0   0   0   0   0   0   0   0   0   0   0   0   0
2019    2019-5  0   0   0   0   0   0   0   0   0   0   0   0   0   0
2019    2019-6  0   0   0   0   0   0   0   0   0   0   0   0   0   0
2019    2019-7  0   0   0   0   0   0   1   1   1   1   0   0   1   1
2019    2019-8  0   0   0   0   0   0   0   2   1   0   1   1   1   1
2019    2019-9  0   0   0   0   0   0   0   0   1   0   0   0   0   0
2019    2019-10 0   0   0   0   0   0   0   0   0   2   1   0   1   0
2019    2019-11 0   0   0   0   0   0   0   0   0   0   1   0   1   1
2019    2019-12 0   0   0   0   0   0   0   0   0   0   0   0   0   0
2020    2020-1  0   0   0   0   0   0   0   0   0   0   0   0   5   3
2020    2020-2  0   0   0   0   0   0   0   0   0   0   0   0   0   6
2020    2020-3  0   0   0   0   0   0   0   0   0   0   0   0   0   0
2020    2020-4  0   0   0   0   0   0   0   0   0   0   0   0   0   0

Considering the above table as row data, I am trying to create a separate table which only filter for a given year(using drop down in cell D5). The expected output should look like as below but getting #Value Error.

CodePudding user response：

You need FILTER() function. Try-

=FILTER(FILTER(Sheet1!B2:P18,Sheet1!A2:A18=D5),Sheet1!B1:P1=D5)

If you want to include side column containing yyyy-m then use VSTACK() and HSTACK() function like-

=LET(x,FILTER(FILTER(Sheet1!B2:P18,Sheet1!A2:A18=D5),Sheet1!B1:P1=D5),y,FILTER(Sheet1!B3:B18,Sheet1!A3:A18=D5),z,FILTER(Sheet1!C2:P2,Sheet1!C1:P1=D5),HSTACK(VSTACK("Year-Month",y),VSTACK(z,x)))