Unique count of IDs where column B contains one word only

[makaveeti]

Hi All,

Sample data and expected results attached.

I require a formula for cell G2 which does a unique count of IDs in column A where column B contains one word only.

Expected results should be 3.

[Bo_Ry]

Try

=COUNT(UNIQUE(FILTER(A2:A17,ISERROR(FIND(" ",B2:B17)))))

[WideBoyDixon]

Not sure how far down your table goes. Inefficient whole-column formula would be:

Please Login or Register  to view this content.

Or you could adjust to suit your data:

Please Login or Register  to view this content.

WBD

[makaveeti]

Thanks Bo_Ry it works but it is counting IDs where cells in column B are blank in my real data. I've updated the sample data to show blank cells.

[makaveeti]

WideBoyDixon,

I've updated the sample data to include blank cells in column B as I missed that out.

[Bo_Ry]

Try

=COUNT(UNIQUE(FILTER(A2:A50,(B2:B50>"")*ISERROR(FIND(" ",B2:B50)))))

[makaveeti]

Bo_Ry - That works perfect.


Also. what formula would I use for column C If I wanted to identify rows where column B only contains 1 word?

I tried the following formula however it returns Yes for blank cells too. =IF(COUNTIF(B2,"* *"),"","Yes")

[WideBoyDixon]

C2 formula:

Please Login or Register  to view this content.

WBD

[makaveeti]

Thank you WBD. That work's perfect.