Forum Discussion

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

COUNTA(UNIQUE(FILTER(

Hello all, 

 

I am trying to write a formula that will counts the number of unique values in Column L:L assuming there filter Sobeys and not Voila. 

 

The below is my formula:

 

=COUNTA(UNIQUE(FILTER('Iri Comp Data'!$H:$H,ISNUMBER(SEARCH("Sobeys",'Iri Comp Data'!$L:$L,ISNUMBER(SEARCH("<>Voila",'Iri Comp Data'!$L:$L

 

I believe the issue is that I can't use <>. The problem is in my data set in Column there is Voila by Sobeys and I don't want to include those in my count.

 

Thank you!

 

  • Renattae_Schmidt 

    The formula isn't complete, but it should probably look like

     

    =COUNTA(UNIQUE(FILTER('Iri Comp Data'!$H:$H,ISNUMBER(SEARCH("Sobeys",'Iri Comp Data'!$L:$L))*ISERROR(SEARCH("Voila",'Iri Comp Data'!$L:$L)))))

1 Reply

  • Renattae_Schmidt 

    The formula isn't complete, but it should probably look like

     

    =COUNTA(UNIQUE(FILTER('Iri Comp Data'!$H:$H,ISNUMBER(SEARCH("Sobeys",'Iri Comp Data'!$L:$L))*ISERROR(SEARCH("Voila",'Iri Comp Data'!$L:$L)))))

Resources