+ Reply to Thread
Results 1 to 8 of 8

Filter Causing SPILL# in IF Formula

  1. #1
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    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.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    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
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    Re: Filter Causing SPILL# in IF Formula

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

  7. #7
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    Re: Filter Causing SPILL# in IF Formula

    Quote Originally Posted by XOR LX View Post
    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.

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Filter Causing SPILL# in IF Formula

    Quote Originally Posted by Fugdkn View Post
    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.

    Quote Originally Posted by Fugdkn View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 08-09-2020, 10:57 AM
  2. Count IF returning spill
    By vini.v4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2020, 08:34 PM
  3. Spill Error with index match formula
    By Daniel_ISS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2020, 09:36 AM
  4. [SOLVED] VBA Advanced Filter causing Excel file to corrupt...
    By Pablos690_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-20-2016, 06:37 PM
  5. Formula to spill value to another cell after reaching a specific amount.
    By pjpankey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-27-2013, 06:03 PM
  6. Allow text to spill into adjacent cells
    By DDR in forum Excel General
    Replies: 13
    Last Post: 04-02-2011, 09:58 AM
  7. spill
    By april27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2006, 12:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1