Count for the same serial of characters

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

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
 
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.
 
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).)
 
Back
Top