Jun 23 2022 08:27 AM
Hello All,
I am trying to count the unique values in column K, assuming column O has west and column C is less <= 200. The below is the formula I am using, but I get a 1.
=COUNTA(UNIQUE(FILTER('Iri Comp Data'!$K:$K,ISNUMBER(SEARCH("West",'Iri Comp Data'!$O:$O,ISNUMBER(SEARCH("<=200",'Iri Comp Data'!$C:$C)))))))
I don't know if I am allowed to search for a range in a isnumber function?
Help? best way to go about what I am trying to achive?
Thanks!
Jun 23 2022 08:57 AM
Solution
In what you posted there's an uncertainty regarding the content of column C in 'Iri Comp Data'. Assuming that column contains Numbers and not Text values, the following should do what you want (sample attached):
=COUNTA(
UNIQUE(
FILTER('Iri Comp Data'!K:K,
('Iri Comp Data'!C:C <= 200)
* ISNUMBER(SEARCH("west",'Iri Comp Data'!O:O))
)
)
)
Sample attached
If this doesn't work please upload & share a workbook, i.e. with OneDrive, Google Drive... and post the link here
Jun 23 2022 08:57 AM
Solution
In what you posted there's an uncertainty regarding the content of column C in 'Iri Comp Data'. Assuming that column contains Numbers and not Text values, the following should do what you want (sample attached):
=COUNTA(
UNIQUE(
FILTER('Iri Comp Data'!K:K,
('Iri Comp Data'!C:C <= 200)
* ISNUMBER(SEARCH("west",'Iri Comp Data'!O:O))
)
)
)
Sample attached
If this doesn't work please upload & share a workbook, i.e. with OneDrive, Google Drive... and post the link here