Closed Thread
Results 1 to 12 of 12

Returning a zero if_empty in a nested FILTER Function

  1. #1
    Registered User
    Join Date
    09-22-2022
    Location
    Brno, CZ
    MS-Off Ver
    365
    Posts
    4

    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,

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,824

    Re: Returning a zero if_empty in a nested FILTER Function

    Wellcome to the forum.

    Easier if you share a workbook, but try 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,0,1}))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    09-22-2022
    Location
    Brno, CZ
    MS-Off Ver
    365
    Posts
    4

    Re: Returning a zero if_empty in a nested FILTER Function

    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.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Returning a zero if_empty in a nested FILTER Function

    We don't need to see your real sheet. Mock something up with NO MORE than 10-20 rows.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,824

    Re: Returning a zero if_empty in a nested FILTER Function

    Well, I did it off the top of my head, so small syntax errors sometimes happen.

    I don't see this solution as a cheat.

    You can provide a small, desensitised version of the workbook - we never expect to see sensitive data!

  6. #6
    Registered User
    Join Date
    09-22-2022
    Location
    Brno, CZ
    MS-Off Ver
    365
    Posts
    4

    Re: Returning a zero if_empty in a nested FILTER Function

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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,824

    Re: Returning a zero if_empty in a nested FILTER Function

    hope you did not take it that way.
    No ... Just saying I'm not perfect!!!

    Having a look now.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,824

    Re: Returning a zero if_empty in a nested FILTER Function

    I'd do it this way:

    =SUMIFS($G$11:$G$21,$C$11:$C$21,M$8,$F$11:$F$21,$L9)

  9. #9
    Registered User
    Join Date
    09-22-2022
    Location
    Brno, CZ
    MS-Off Ver
    365
    Posts
    4

    Re: Returning a zero if_empty in a nested FILTER Function

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

    Thanks again

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,824

    Re: Returning a zero if_empty in a nested FILTER Function

    Been there - got the T-shirt.

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  11. #11
    Registered User
    Join Date
    07-17-2012
    Location
    iran
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Returning a zero if_empty in a nested FILTER Function

    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)),""),"")

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,824

    Re: Returning a zero if_empty in a nested FILTER Function

    1. The thread was solved in 2022, but thanks.
    2. If you are using FILTER, you don't use Excel 2010 any longer - please update your forum profile.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. FILTER Function only returning top 10 results?
    By Shodan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2021, 10:27 AM
  2. WEEKDAY function returning 42000+ when nested inside IF
    By Gingercat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2019, 08:47 PM
  3. Nested IF function returning #N/A
    By Pivoter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2016, 02:42 PM
  4. Nested If function returning FALSE instead of number
    By erimhast in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2015, 07:47 PM
  5. [SOLVED] VBA Filter Function Returning Same Values
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-10-2015, 02:47 AM
  6. Nested IF function returning N/A for some cells
    By leafs4life22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2013, 02:14 AM
  7. Nested IF Function Returning All Zero
    By lady_Jane in forum Excel General
    Replies: 2
    Last Post: 06-29-2011, 04:22 PM

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