how to count this in this unusual situation

[iksuinje]

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

 

[Sheeloo]

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.

 

[Ron Rosenfeld]

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

Since you have formulas in the cells, none of them will be "blank".

To count the number of numeric values in E1:E10, you could use this:

=SUMPRODUCT(--ISNUMBER(-E1:E10))

Note that prior to Excel 2007, you cannot refer to an entire column using this
formula.

--ron