Count for the same serial of characters
 

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

--
H. Frank Situmorang

Count for the same serial of characters
 

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

regards
FSt1

"Frank Situmorang" wrote:

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

--
H. Frank Situmorang

Count for the same serial of characters
 

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.

--
Biff
Microsoft Excel MVP


"Frank Situmorang" wrote in message
...
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

--
H. Frank Situmorang

Count for the same serial of characters
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP

"Frank Situmorang" wrote in message
...
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

--
H. Frank Situmorang

--

Dave Peterson

Count for the same serial of characters
 

Just a typo alert:
(I changed the dots to commas (my list separator).)

Thanks, Dave!


--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP

"Frank Situmorang" wrote in message
...
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

--
H. Frank Situmorang

--

Dave Peterson