How to count unique instances (row wise) based on a criterion.

[kwaz]

Hi all I have got a a large spreadsheet, and I am trying to count the unique occurrences of a based on a criterion. Below an example

hi@gmail.com	hi@gmail.com	hay@gmail.com	2​	​
hi@yahoomail.com	up@gmail.com		1​

for the first two, the first two cells are duplicates and should be counted as 1. so the count should be two as seen in the cell farthest to the right
while the second row should return one, ignoring all mails that are not of @gmail domain and any blank space.

I have tried this but it doesn't work:

=UNIQUE(COUNTIF(array,"*@gmail*"),1,FALSE)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COLUMNS(UNIQUE(FILTER(A2:C2,ISNUMBER(SEARCH("@gmail",A2:C2))),1))

 

[kwaz]

Hi & welcome to MrExcel.
How about

=COLUMNS(UNIQUE(FILTER(A2:C2,ISNUMBER(SEARCH("@gmail",A2:C2))),1))

Thank you so much for your swift response. This works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.