Returning a zero if_empty in a nested FILTER Function

Hi,

I have a filter function which sums the values from the fifth column of the returned array:

=SUM(FILTER(FILTER(Tasks!$B$34:$F$171,(Tasks!$B$34:$B$171=G$1)*(Tasks!$E$34:$E$171=$A2)), {0,0,0,0,1}))

In both of the filter functions, I have left the optional if_empty variable blank. With this value blank it returns #CALC is there is no data found, presumably due to the lower order filter function returning an empty array.

If there is matching data, the formula works as expected.

So I thought if I was to put a 0 in the if_empty location, then I would get 0 back as a result, if the filter finds nothing. As it turns out I get #VALUE instead.

I have tried putting a 0 in both or one or the other filter formalus, but the result is the same.

Does anyone have any idea how I can make the formula return a 0 if there are no results from my filter functions?

Thanks,

Hi, many thanks for your reply.

Unfortunately the workbook contains confidential data, I cannot easily share it.

I think your formula had a small syntax error. (the value if error was part of the FILTER function, rather than the IFERROR function) I tried this:

=SUM(IFERROR(FILTER(FILTER(Tasks!$B$34:$F$171,(Tasks!$B$34:$B$171=G$1)*(Tasks!$E$34:$E$171=$A2)),{0,0,0,0,1}),0))

And it worked.

However, I feel like it's a bit of a cheat :-) Do you think its possible for a empty result from a nested filter to return the value from the if_empty part of the first one and that get passed to the higher level one, and then return the if_empty value too, or is it always going to see the zero from the low level function as empty data and complain that it does not support empty arrays?

If so, I can live with the iferror solution.

Thanks again.

Yes, there was no criticism regarding the syntax error :-) hope you did not take it that way.

I would also not see it as a cheat if there is no way to adjust the formula not to return an error in the circumstances. I'm just curious if there is any other way other than catching the error.

I have attached a desensitized version. The problem cell is O10

Many thanks.

Yes, that works and it is much simpler. Suppose I got carried away with functions more complicated than required for the problem.

Thanks again

Hi,
this work for me.
=IF(FILTER(Data!B:AN,((Data!B:B=Print!AO1)*(Data!C:C=Print!AP1)),"")<>0,FILTER(Data!B:AN,((Data!B:B=Print!AO1)*(Data!C:C=Print!AP1)),""),"")