Formula help, counting unique occurences.

[J2Reed]

I am looking for a formula that will count unique occurrences that start with W81UTH, ignoring duplicates and blank cells.
Also looking for a formula that will count unique occurrences that does NOT start with W81UTH, ignoring duplicates and blank cells.
I want to achieve the desired results below in cells D3 and D4.

 

[DanteAmor]

Try this ARRAY formula (Entered with Ctrl+Shift+Enter)

Book1
	A	B	C	D
1	W81UTHa1
2	W81UTHa2
3	W81UTHa3		STARTS WITH W81UTH	3
4			DOES NOT START WITH W81UTH	9
5	W81UTHa1
6	W81UTHa2
7	W81UTHa3
8
9	W50abcd1
10	W50abcd2
11	W50abcd3
12
13	W50abcd1
14	W50abcd2
15	W50abcd3
16	W50abcd4
17	W50abcd5
18	W50abcd6
19	W50abcd7
20	W50abcd8
21	W50abcd9
Hoja15

Cell Formulas
Range	Formula
D3	D3	{=SUM(IF(FREQUENCY(IF(LEFT($A$1:$A$21,6)="W81UTH",MATCH($A$1:$A$21,$A$1:$A$21,0)),ROW($A$1:$A$21)-ROW($A$1)+1),1))}
D4	D4	{=SUM(IF(FREQUENCY(IF((A1:A21<>"")*(LEFT($A$1:$A$21,6)<>"W81UTH"),MATCH($A$1:$A$21,$A$1:$A$21,0)),ROW($A$1:$A$21)-ROW($A$1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[realdemigod]

You can try removing the duplicates in the column from the Data on the menu and then use the following.

In the formula below use the '?' as many times as the remaining text of the string. This would work if the length is same for all the cells in which the string is present.

COUNTIF(A2:A28,"W81UTH?????")

For the second thing, COUNTA(A2:A28)-the above formula

 

[hajiali]

Another approach would be using column B or any other columns as helper. In column
B1"=LEFT(A1,6)" Drag down then
D3="=COUNTIF($B$1:$B$28,"W81UTH")"
D4="=COUNTIF($A$1:$A$28,"<>")-COUNTIF($C$1:C28,"W81UTH")"

to clean it up you can just hide the Helper Column B

 

[J2Reed]

Thanks for the help, it worked.

 

[DanteAmor]

Youre welcome!