FILTER problem

[sharshra]

I´m using FILTER function. It returns 0 when there is no value. I have used the optional [if empty], but still get 0. I´m sure it must be some trivial mistake by me. Can the experts help please?

Source table:

excel problems.xlsx
	B	C
2	name	dept
3	abc	finance
4	bcd	hr
5	cde	procurement
6	def	sales
7	abc	sales
8		hr
9	mno	security
10		procurement
11		hr
12	efg	finance
filter

Filter:

excel problems.xlsx
	E	F
3	bcd	hr
4	0	hr
5	0	hr
filter

Cell Formulas
Range	Formula
E3:F5	E3	=FILTER($B$3:$C$12,$C$3:$C$12="hr","no name")
Dynamic array formulas.

 

[hagia_sofia]

Hello, the optional argument refers to the situation when there is nothing to return. The result could be obtained e.g. like:

=IF(FILTER($B$3:$C$12,$C$3:$C$12="hr")="","no name",FILTER($B$3:$C$12,$C$3:$C$12="hr"))

 

[sharshra]

Thanks, @hagia_sofia for quick response. I'm using IF function to get the desired output. But I thought optional argument [if empty] is meant for doing this instead of using a lengthy IF formula. Is there any reason why [if empty] is not working?

 

[Peter_SSs]

Perhaps one of these?

24 10 12.xlsm
	B	C	D	E	F	G	H	I
2	name	dept
3	abc	finance		bcd	hr		bcd	hr
4	bcd	hr			hr		no name	hr
5	cde	procurement			hr		no name	hr
6	def	sales
7	abc	sales
8		hr
9	mno	security
10		procurement
11		hr
12	efg	finance
Filter

Cell Formulas
Range	Formula
E3:F5	E3	=FILTER($B$3:$C$12,$C$3:$C$12="hr","")&""
H3:I5	H3	=FILTER(IF(B3:C12="","no name",B3:C12),C3:C12="hr","")
Dynamic array formulas.

 

[Peter_SSs]

Is there any reason why [if empty] is not working?

The if empty argument for the FILTER function is for if the whole filter result is empty (that is nothing is returned by the whole filter), not if particular cells in the filtered range are empty.

 

[sharshra]

Thanks to @hagia_sofia & @Peter_SSs.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.