CountA will count cells that include the empty string (ie ""). Sounds like
you have formulae in column A that evaluate to "" that are being counted by
the counta function. Perhaps:

=IF(ROWS($1:1)<=SUM(--($A$1:$A$500<"")),INDEX($A$1:$A$500,SMALL(IF($A$1 :$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

will give the results you're after (I'm shooting from the hip - It's a bit
late and I didn't test this).


"Davidm" wrote:

Hi I am using a formula in Coulumn B which is
=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

This is an array formula that must be inserted with:
CNTRL-SHFT-ENTER
rather than the
ENTER key

This is to bring to the top of column all entries that have a positive
reponse to an IF formula in column A which puts "" if false. The above
formula works well but in the cells below the numbers it comes up with #NUM!
in all the cells below
How do I fix it so those cells are blank in column B like in A

Regards David