Count for the same serial of characters

[Frank Situmorang]

Hello,

I have in my spreadsheet as follows:

A B C
1 PO200
2 PO200
3. PO201
4. PO202
--------

Total Purchase order = 3

As you can see above the total is only 3 although thre are 4, because PO200
is only 1 for the same serial of characters

My question is what is the function in excel the can count for only
difference in characters in those cells.

Thanks in advance

 

[FSt1]

hi
you could use the advanced filter.
or....
see this site.
http://www.officearticles.com/excel/count_unique_values_in_microsoft_excel.htm

regards
FSt1

 

[T. Valko]

Try one of these:

If there will not be any empty cells within the range:

=SUMPRODUCT(1/COUNTIF(B1:B4.B1:B4))

If there might be empty cells within the range:

=SUMPRODUCT((B1:B4<>"")/COUNTIF(B1:B4.B1:B4&""))

Note that either is slow to calculate on large ranges.

 

[Dave Peterson]

Just a typo alert:

=SUMPRODUCT(1/COUNTIF(B1:B4,B1:B4))
=SUMPRODUCT((B1:B4<>"")/COUNTIF(B1:B4,B1:B4&""))

(I changed the dots to commas (my list separator).)

 

[T. Valko]

Just a typo alert:

(I changed the dots to commas (my list separator).)

Thanks, Dave!