Forum Discussion

Renattae_Schmidt's avatar
Renattae_Schmidt
Copper Contributor
Jun 30, 2022
Solved

Unique filter designated number range

Hello everyone!

 

I am trying to create a filter that will count all the unique values assuming the values in column C are between 300 and 649. I can get the formula to work fine if I just use one value to search for, but I can't seem to figure out how to add the second value. I trying doing the below but it's not working.

 

COUNTA( UNIQUE( FILTER( 'Iri Comp Data'!$K:$K, ('Iri Comp Data'!$C:$C>=300) 'Iri Comp Data'!$K:$K, ('Iri Comp Data'!$C:$C<=649) * ISNUMBER(SEARCH("Foodland",'Iri Comp Data'!$O:$O) ) * ISNUMBER( SEARCH( "ON",'Iri Comp Data'!$S:$S )) )))

 

And ideas how to fix it?

Thanks!

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 30, 2022

    Hi Renattae_Schmidt 

     

    Try the following:

    =COUNTA(
    UNIQUE(
        FILTER('Iri Comp Data'!$K:$K,
            ('Iri Comp Data'!$C:$C >= 300) *
            ('Iri Comp Data'!$C:$C <= 649) *
            ISNUMBER(SEARCH("Foodland",'Iri Comp Data'!$O:$O)) *
            ISNUMBER(SEARCH("ON",'Iri Comp Data'!$S:$S))
        )
    )
)

    If this doesn't work please share your workbook with i.e. https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07, Google Drive... and post the link here - Thanks

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 30, 2022

    Renattae_Schmidt 

    =COUNTA( UNIQUE( FILTER( 'Iri Comp Data'!$K:$K, ('Iri Comp Data'!$C:$C>=300) * ('Iri Comp Data'!$C:$C<=649) * ISNUMBER(SEARCH("Foodland",'Iri Comp Data'!$O:$O)) * ISNUMBER( SEARCH("ON",'Iri Comp Data'!$S:$S)))))

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 30, 2022

    Hi Renattae_Schmidt 

     

    Try the following:

    =COUNTA(
    UNIQUE(
        FILTER('Iri Comp Data'!$K:$K,
            ('Iri Comp Data'!$C:$C >= 300) *
            ('Iri Comp Data'!$C:$C <= 649) *
            ISNUMBER(SEARCH("Foodland",'Iri Comp Data'!$O:$O)) *
            ISNUMBER(SEARCH("ON",'Iri Comp Data'!$S:$S))
        )
    )
)

    If this doesn't work please share your workbook with i.e. https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07, Google Drive... and post the link here - Thanks

Resources