Try this;
Put this in the next column after you have pasted the result as vaues
(assuming you pasted the result in column H from row 1 with no lable)
=IF(H1="",0,1)
Copy it down and then Sum the column

The result of your formula is either returning a number as text or "" which
is messing up your attempts to count.

"iksuinje" wrote:

Hi,

I am trying to count unique value in a list under column E
E
436
600

514

548
.
.
.
The value in the above columb B is the result of certain formula such as
IF(D9="","",IF(ISNUMBER(MATCH(LEFT(D9,FIND("/",D9)-1),INDIRECT("E"&B$4):E8,0)),"",LEFT(D9,FIND("/",D9)-1)))

When I tred to count this using "counta", the reulst was wierd... it
returned the number of even blank cell....

So I copy the column and paste it with 'paste special' as value checked to
paste only result of the cells. But when pasted, each numbers in the cells
has a certain message with ! mark saying " the number in the cell is
formatted as text or preceded by an apostrophe". For blank cells, there is no
! mark. But when counting the colum with 'counta', the blank cells are also
counted... how to solve this problem?

thanks in advance