Forum Discussion

mantwi2's avatar
mantwi2
Copper Contributor
Jul 16, 2022

Nested filter

Can someone please tell me why any of these formulas arent working? They seem to be correct. 

 

Thank you: 

 

=FILTER(FILTER('Version Ctrl'!$B$2:$B$27,'Version Ctrl'!$B$2:$B$27<>""),'Version Ctrl'!$B$2:$B$27<=A3)

 

=FILTER('Version Ctrl'!$B$2:$B$27,AND('Version Ctrl'!$B$2:$B$27<=A3,'Version Ctrl'!$B$2:$B$27<>""))

Formulas and Functions

1 Reply

  • mathetes's avatar
    mathetes
    Gold Contributor
    Jul 16, 2022

    mantwi2 

     

    Not for sure, since I've not got access to your spreadsheet/workbook. You might try removing the absolute references.

     

    =FILTER('Version Ctrl'!$B$2:$B$27,AND('Version Ctrl'!$B$2:$B$27<=A3,'Version Ctrl'!$B$2:$B$27<>""))

    That isn't the way to combine two criteria in the FILTER function. Instead, the criteria would be combined with an asterisk between them

    ....('Version Ctrl'!$B$2:$B$27<=A3)*('Version Ctrl'!$B$2:$B$27<>"")...

     

    Here are two good resources to learn FILTER:

    https://www.youtube.com/watch?v=9I9DtFOVPIg

    and

    https://exceljet.net/excel-functions/excel-filter-function

     

    If you're not able to resolve it, see if you can come back and post either the actual file OR a mockup, just ensuring no confidential data is in it. If you can't post it here, use OneDrive or GoogleDrive and give us a link.

Resources