Can countif formula use wildcard and only return number of unique entries with the wildcard?

[torchedya]

So I have a spreadsheet that has a column that has device names hostnames. For example the names look similar to these: ups-park-5g98, emu-chic-8t56. I am using a countif formula to count all entries that have emu in the name using "*emu*", but while doing so I see that some of the emu names are listed multiple times. I only want the formula to count each name once. Is there a way to do this in the formula? Remember that all the hostnames in column do not have emu in them and the emu hostnames may not be in the same lines in the column every time as this is coming from a report that is ran weekly.

Thanks!!

 

[Weazel]

maybe something like....

Excel 2016 (Windows) 32 bit

	A	B	C
2	emu-chic-8t56		3
3	chic-emu-254
4	emu-chic-8t56
5	chic-8t52-emu
6	chic-8t52-emu
7
8
9
10

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("emu",A2:A10)),MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[torchedya]

That worked! Thanks!!!

 

[Weazel]

you're welcome, glad it worked for you

 

[torchedya]

	A	B	C
2	emu-chic-8t56	ch	3
3	chic-emu-254	il
4	emu-chic-8t56	ch
5	chic-8t52-emu	il
6	chic-8t52-emu	ch
7
8
9
10

<colgroup><col width="64" style="width: 48pt;" span="4">
<tbody>




























































</tbody>

Lets say column B is the server that the device resides on. I still want to use the formula you gave but I want to add the condition that it only counts the emu's on server ch. Is this possible?