Exclude False from Array Formula

[johnmpc]

Hi All,

Should be simple hopefully.

I have this formula

=IF(C3:BH3="SICK",C2:BH2)

It returns the date in the cell range C2:BH2 if the cell below in C3:BH3 has "SICK".

Is there an edit to exclude the False and Blank results from an array?

Thanks

 

[JamesCanale]

MrExcelPlayground7.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	1/1/2022	1/2/2022	1/3/2022	1/4/2022	1/5/2022	1/6/2022	1/7/2022	1/8/2022	1/9/2022	1/10/2022	1/11/2022	1/12/2022	1/13/2022	1/14/2022	1/15/2022
3		Sick		Sick	Sick					Sick				Sick
4	1/2/2022	1/4/2022	1/5/2022	1/10/2022	1/14/2022
Sheet5

Cell Formulas
Range	Formula
D2:Q2	D2	=C2+1
C4:G4	C4	=FILTER(IF(C3:Q3="Sick",C2:Q2),SIGN(IF(C3:Q3="Sick",C2:Q2)),"")
Dynamic array formulas.

 

[Fluff]

How about

=IF(C3:BH3="SICK",C2:BH2,"")

 

[johnmpc]

How about

=IF(C3:BH3="SICK",C2:BH2,"")

This just turns False to Blank.

MrExcelPlayground7.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	1/1/2022	1/2/2022	1/3/2022	1/4/2022	1/5/2022	1/6/2022	1/7/2022	1/8/2022	1/9/2022	1/10/2022	1/11/2022	1/12/2022	1/13/2022	1/14/2022	1/15/2022
3		Sick		Sick	Sick					Sick				Sick
4	1/2/2022	1/4/2022	1/5/2022	1/10/2022	1/14/2022
Sheet5

Cell Formulas
Range	Formula
D2:Q2	D2	=C2+1
C4:G4	C4	=FILTER(IF(C3:Q3="Sick",C2:Q2),SIGN(IF(C3:Q3="Sick",C2:Q2)),"")
Dynamic array formulas.

Pretty sure this is exactly what i want, Thank you. Will report back.

 

[Fluff]

This just turns False to Blank.

I may have misunderstood what you were after.

 

[Eric W]

James' formula can be shortened a bit:

=FILTER(C2:Q2,C3:Q3="Sick")

 

[johnmpc]

James' formula can be shortened a bit:

=FILTER(C2:Q2,C3:Q3="Sick")

This is so simple. Thanks. i will definitely use in the future. However, i need it to remove Blank results from the array also, which James' formula does.

MrExcelPlayground7.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	1/1/2022	1/2/2022	1/3/2022	1/4/2022	1/5/2022	1/6/2022	1/7/2022	1/8/2022	1/9/2022	1/10/2022	1/11/2022	1/12/2022	1/13/2022	1/14/2022	1/15/2022
3		Sick		Sick	Sick					Sick				Sick
4	1/2/2022	1/4/2022	1/5/2022	1/10/2022	1/14/2022
Sheet5

Cell Formulas
Range	Formula
D2:Q2	D2	=C2+1
C4:G4	C4	=FILTER(IF(C3:Q3="Sick",C2:Q2),SIGN(IF(C3:Q3="Sick",C2:Q2)),"")
Dynamic array formulas.

Absolutely spot on. Thanks.

 

[James Clear]

Just trying with old method ....

Data - C2 to Q3 as per OP

01-01-2022​

02-01-2022​

03-01-2022​

04-01-2022​

05-01-2022​

06-01-2022​

07-01-2022​

08-01-2022​

09-01-2022​

10-01-2022​

11-01-2022​

12-01-2022​

13-01-2022​

14-01-2022​

15-01-2022​

Sick

Sick

Sick

Sick

Sick

02-Jan-22​

04-Jan-22​

05-Jan-22​

10-Jan-22​

14-Jan-22​

02-Jan-22​

04-Jan-22​

05-Jan-22​

10-Jan-22​

14-Jan-22​

C6 : Q6 =IFERROR(INDEX($C$2:$Q$2,AGGREGATE(15,6,(COLUMN($C$2:$Q$2)-COLUMN($C$2)+1)/(C$3="Sick"),COLUMNS($C6:C6))),"")
C7 : Q7 =IFERROR(SMALL($C$6:$Q$6,COLUMNS($C7:C7)),"")