Filter Filter to return only some columns

[ShalDRH]

Hello!

I am trying to extract data in the following set with only the month columns in the array as return columns.

In the two examples below the top left 2 values of CO# and Data are my variables and the yellow row of each array is what I want it to find as well as the corresponding month in the top row. The transposed example "answers" I want to get are in the green boxes to the left of the data sets.

1. For the first data set I want to know which months have closings for CO# 56068 and how many closings are in each month.
2. For the second data set I'm looking for which months have starts for CO# 56058 and how many in each month.

However, I keep getting the #value! response with my formula - =TRANSPOSE(FILTER(FILTER(D1:J21,(D1:D21=B1)*(E1:E21=B2)*(F2:J21>0),"0"),{0,0,1,1,1,1,1})) which seems to indicate my include arguments are wrong. I don't know how to change them so they are correct.

I appreciate your help!
Shalon

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Co#	56068		Co#	Data	OCT 2021	NOV 2021	DEC 2021	JAN 2022	FEB 2022		Co#	56058		Co#	Data	OCT 2021	NOV 2021	DEC 2021	JAN 2022	FEB 2022
2	Data	Closings		56058	Sales	8	7	12	7	8		Data	Starts		56058	Sales	8	7	12	7	8
3				56058	Starts	14	8	7	8	8					56058	Starts	14	8	7	8	8
4	OCT 2021	5		56058	Closings	8	6	9	8	6		OCT 2021	14		56058	Closings	8	6	9	8	6
5	DEC 2021	4		56063	Sales	0	0	0	0	0		NOV 2021	8		56063	Sales	0	0	0	0	0
6				56063	Starts	0	0	0	0	0		DEC 2021	7		56063	Starts	0	0	0	0	0
7	#VALUE!			56063	Closings	0	1	1	0	0		JAN 2022	8		56063	Closings	0	1	1	0	0
8				56068	Sales	1	0	0	0	0		FEB 2022	8		56068	Sales	1	0	0	0	0
9				56068	Starts	0	0	0	0	0					56068	Starts	0	0	0	0	0
10				56068	Closings	5	0	4	0	0					56068	Closings	5	0	4	0	0
11				56723	Sales	6	5	9	5	5					56723	Sales	6	5	9	5	5
12				56723	Starts	10	8	9	13	5					56723	Starts	10	8	9	13	5
13				56723	Closings	6	5	6	4	4					56723	Closings	6	5	6	4	4
14				56726	Sales	6	3	7	8	7					56726	Sales	6	3	7	8	7
15				56726	Starts	8	9	7	8	8					56726	Starts	8	9	7	8	8
16				56726	Closings	1	5	2	6	3					56726	Closings	1	5	2	6	3
17				56071	Sales	6	8	5	5	5					56071	Sales	6	8	5	5	5
18				56071	Starts	13	3	8	6	7					56071	Starts	13	3	8	6	7
19				56071	Closings	0	0	0	0	0					56071	Closings	0	0	0	0	0
20				56043	Sales	8	6	11	6	6					56043	Sales	8	6	11	6	6
21				56043	Starts	12	6	8	6	10					56043	Starts	12	6	8	6	10
22
Sheet1

Cell Formulas
Range	Formula
A7	A7	=TRANSPOSE(FILTER(FILTER(D1:J21,(D1:D21=B1)*(E1:E21=B2)*(F2:J21>0),"0"),{0,0,1,1,1,1,1}))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$D$1:$J$72	A7

 

[Eric W]

Try:

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Co#	56068		Co#	Data	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22		Co#	56058		Co#	Data	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22
2	Data	Closings		56058	Sales	8	7	12	7	8		Data	Starts		56058	Sales	8	7	12	7	8
3				56058	Starts	14	8	7	8	8					56058	Starts	14	8	7	8	8
4	Oct-21	5		56058	Closings	8	6	9	8	6		Oct-21	14		56058	Closings	8	6	9	8	6
5	Dec-21	4		56063	Sales	0	0	0	0	0		Nov-21	8		56063	Sales	0	0	0	0	0
6				56063	Starts	0	0	0	0	0		Dec-21	7		56063	Starts	0	0	0	0	0
7				56063	Closings	0	1	1	0	0		Jan-22	8		56063	Closings	0	1	1	0	0
8				56068	Sales	1	0	0	0	0		Feb-22	8		56068	Sales	1	0	0	0	0
9				56068	Starts	0	0	0	0	0					56068	Starts	0	0	0	0	0
10				56068	Closings	5	0	4	0	0					56068	Closings	5	0	4	0	0
11				56723	Sales	6	5	9	5	5					56723	Sales	6	5	9	5	5
12				56723	Starts	10	8	9	13	5					56723	Starts	10	8	9	13	5
13				56723	Closings	6	5	6	4	4					56723	Closings	6	5	6	4	4
14				56726	Sales	6	3	7	8	7					56726	Sales	6	3	7	8	7
15				56726	Starts	8	9	7	8	8					56726	Starts	8	9	7	8	8
16				56726	Closings	1	5	2	6	3					56726	Closings	1	5	2	6	3
17				56071	Sales	6	8	5	5	5					56071	Sales	6	8	5	5	5
18				56071	Starts	13	3	8	6	7					56071	Starts	13	3	8	6	7
19				56071	Closings	0	0	0	0	0					56071	Closings	0	0	0	0	0
20				56043	Sales	8	6	11	6	6					56043	Sales	8	6	11	6	6
21				56043	Starts	12	6	8	6	10					56043	Starts	12	6	8	6	10
22
Sheet20

Cell Formulas
Range	Formula
A4:A5,L4:L8	A4	=TRANSPOSE(FILTER(F1:J1,FILTER(F2:J21,(D2:D21=B1)*(E2:E21=B2))>0))
B4:B5,M4:M8	B4	=INDEX(F2:J21,MATCH(1,(D2:D21=B1)*(E2:E21=B2),0),MATCH(A4#,F1:J1,0))
Dynamic array formulas.

I found a single-cell formula instead of 2, but it was pretty ugly.

 

[ShalDRH]

Try:

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Co#	56068		Co#	Data	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22		Co#	56058		Co#	Data	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22
2	Data	Closings		56058	Sales	8	7	12	7	8		Data	Starts		56058	Sales	8	7	12	7	8
3				56058	Starts	14	8	7	8	8					56058	Starts	14	8	7	8	8
4	Oct-21	5		56058	Closings	8	6	9	8	6		Oct-21	14		56058	Closings	8	6	9	8	6
5	Dec-21	4		56063	Sales	0	0	0	0	0		Nov-21	8		56063	Sales	0	0	0	0	0
6				56063	Starts	0	0	0	0	0		Dec-21	7		56063	Starts	0	0	0	0	0
7				56063	Closings	0	1	1	0	0		Jan-22	8		56063	Closings	0	1	1	0	0
8				56068	Sales	1	0	0	0	0		Feb-22	8		56068	Sales	1	0	0	0	0
9				56068	Starts	0	0	0	0	0					56068	Starts	0	0	0	0	0
10				56068	Closings	5	0	4	0	0					56068	Closings	5	0	4	0	0
11				56723	Sales	6	5	9	5	5					56723	Sales	6	5	9	5	5
12				56723	Starts	10	8	9	13	5					56723	Starts	10	8	9	13	5
13				56723	Closings	6	5	6	4	4					56723	Closings	6	5	6	4	4
14				56726	Sales	6	3	7	8	7					56726	Sales	6	3	7	8	7
15				56726	Starts	8	9	7	8	8					56726	Starts	8	9	7	8	8
16				56726	Closings	1	5	2	6	3					56726	Closings	1	5	2	6	3
17				56071	Sales	6	8	5	5	5					56071	Sales	6	8	5	5	5
18				56071	Starts	13	3	8	6	7					56071	Starts	13	3	8	6	7
19				56071	Closings	0	0	0	0	0					56071	Closings	0	0	0	0	0
20				56043	Sales	8	6	11	6	6					56043	Sales	8	6	11	6	6
21				56043	Starts	12	6	8	6	10					56043	Starts	12	6	8	6	10
22
Sheet20

Cell Formulas
Range	Formula
A4:A5,L4:L8	A4	=TRANSPOSE(FILTER(F1:J1,FILTER(F2:J21,(D2:D21=B1)*(E2:E21=B2))>0))
B4:B5,M4:M8	B4	=INDEX(F2:J21,MATCH(1,(D2:D21=B1)*(E2:E21=B2),0),MATCH(A4#,F1:J1,0))
Dynamic array formulas.

I found a single-cell formula instead of 2, but it was pretty ugly.

Thanks so much Eric!

This 2 cell solution worked perfectly and I'm absolutely ok with ditching the single-cell formula. I plugged it into my actual data set and tested on numerous options with everything working as intended. Much appreciated.

Shalon

 

[Eric W]

Happy to help! Thanks for the feedback.