# Filter Causing SPILL# in IF Formula

1. ## Filter Causing SPILL# in IF Formula

Hi. I am quite confused as to why I can't seem to get the filter function to work properly in my if formula. In my attached workbook, you will see that I first use the Unique function to find out how many ranges there are (2), and then use the filter function to find out how many products in those ranges have shortages > 0 (one has 3 shortages, the other 0). I used the formula "=COUNTA(FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0))" to basically make an AND statement where it will only include the rows who range matches the one to the left (i.e. F2, which is Range 1 in this case) and ones with shortages > 0, which is 3 for Range 1. This part works fine, but naturally I get an issue when I try to use COUNTA for Range 2 because no shortage is found as there are 0 shortages (i.e. no rows that meet the filter condition), so it ends up returning a 0, but because the "0" counts as one cell, COUNTA returns 1 when I really want it to return 0.

I then tried using an IF formula to solve this:
"=IF(FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)=0,0,COUNTA(FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)=0))"

It works fine for range 2 (it gives me back 0), but for some reason it gives me a spill error when I try to use this same formula for Range 1. I don't understand why it decided to try and return the range of cells for Range 1 before it bothers to evaluate the condition. I know I am very close to a solution but I can't figure this problem out. I am very confused, and quite annoyed, so any help is much appreciated.  Register To Reply

2. ## Re: Filter Causing SPILL# in IF Formula

Hi,

The problem is that COUNTA also counts error values. Perhaps, in M2:

=IFERROR(ROWS(FILTER(A\$2:A\$21,(A\$2:A\$21=F2)*(C\$2:C\$21>0))),0)

and copied down.

Regards  Register To Reply

3. ## Re: Filter Causing SPILL# in IF Formula

Ah, I wouldn't have thought to use ROWS to get around the issue! Thanks so much.

By any chance though, do you know why my attempted fix was giving me a spill error in the IF function (I can get around the problem using your method now, but I am still a bit puzzled as to why using FILTER for an IF condition left me with a spill in one case, but the correct answer in another). If not, no worries.  Register To Reply

4. ## Re: Filter Causing SPILL# in IF Formula

You get a #SPILL! error in H2 because the FILTER function is attempting to return an array of 3 values, i.e. this part:

FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)

is resolving to the vertical array:

{"Range 1","Range 1","Range 1"}

which means that

FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)=0

resolves to an array of three Boolean values, i.e.

{FALSE;FALSE;FALSE}

So altogether you have:

=IF({FALSE;FALSE;FALSE},0,COUNTA({FALSE;FALSE;FALSE}))

which is

=IF({FALSE;FALSE;FALSE},0,3)

i.e.

{3;3;3}

Regards  Register To Reply

5. ## Re: Filter Causing SPILL# in IF Formula

Try this instead. Filter the Values rather than the range name.

=COUNT(FILTER(\$C\$2:\$C\$21,(\$A\$2:\$A\$21=F2)*(\$C\$2:\$C\$21>0),""))

The spill is not present on the "Range 2" formula as it returns 1 item. If you added some positive values to Range 2 items it to would spill  Register To Reply

6. ## Re: Filter Causing SPILL# in IF Formula

Ah thanks Andy. That would have also been a better decision. Thanks a bunch.  Register To Reply

7. ## Re: Filter Causing SPILL# in IF Formula Originally Posted by XOR LX You get a #SPILL! error in H2 because the FILTER function is attempting to return an array of 3 values, i.e. this part:

FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)

is resolving to the vertical array:

{"Range 1","Range 1","Range 1"}

which means that

FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)=0

resolves to an array of three Boolean values, i.e.

{FALSE;FALSE;FALSE}

So altogether you have:

=IF({FALSE;FALSE;FALSE},0,COUNTA({FALSE;FALSE;FALSE}))

which is

=IF({FALSE;FALSE;FALSE},0,3)

i.e.

{3;3;3}

Regards
So what you're saying is that "=IF({FALSE;FALSE;FALSE},0,3)" rather than returning "=IF(FALSE,0,3)" and just giving me 3, it instead decides to fill the array {FALSE;FALSE;FALSE} with 3 for each item?

Well, that certainly explains why it was acting up. Suppose in the future, however, that I did want to use an array in such a way that the result "=IF({FALSE;FALSE;FALSE},0,3)" would give me "=IF(FALSE,0,3)". What would I need to do to get {FALSE;FALSE;FALSE} to return me FALSE? Could I maybe use the UNIQUE function on it or something like that, or would I be better off just writing the code differently (like you and Andy have suggested) in a case like that? (just trying to think ahead in case I find myself in a similar situation in the future that is more complicated but similar to this).

Thanks ever so much.  Register To Reply

8. ## Re: Filter Causing SPILL# in IF Formula Originally Posted by Fugdkn So what you're saying is that "=IF({FALSE;FALSE;FALSE},0,3)" rather than returning "=IF(FALSE,0,3)" and just giving me 3, it instead decides to fill the array {FALSE;FALSE;FALSE} with 3 for each item?
Correct. Originally Posted by Fugdkn What would I need to do to get {FALSE;FALSE;FALSE} to return me FALSE?
In general, you need to use a function which returns a single value from that array, e.g. COUNT, OR, AND.

Regards  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 