#Num! appears at the end of correct entries

[Davidm]

Hi I am using a formula in Coulumn B which i
=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

 

[JMB]

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

 

[T. Valko]

in the cells below the numbers it comes up with #NUM!

So, the formula is returning NUMBERS?

Change COUNTA to COUNT.

It's also slightly more efficient if you move this portion:

-MIN(ROW($A$1:$A$500))+1

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

 

[Davidm]

I have changed the counta to only count in the formula and it now seems to work
thanks for your help I will still have a look at what you have written
Thanks