How can I compare an item to an array of cells but skip blanks?

I am trying to use this formula to compare cell A3 to the values in cells A4 to A20 but I don't want to compare it to any blank cells. This is the formula I tried but it fails with a #VALUE error. =SUMPRODUCT(($A3>($A$4:$A$20*NOT(ISBLANK($A$4:$A$20)))))>0 I'm sure it fails because it's trying to multiply text by a number. However, stepping through the formula I noticed the blank cells are being treated as a value '0' and A3 is always going to be 'greater' than '0'. The goal is to see if A3 is out of order. TIA

In order to compare, using SUBSTITUTE to replace "-" then range to become value, then SUM

=SUMPRODUCT(--(SUBSTITUTE($A$4:$A$20,"-","") < SUBSTITUTE($A3,"-","")))

Originally Posted by bebo021999

In order to compare, using SUBSTITUTE to replace "-" then range to become value, then SUM

=SUMPRODUCT(--(SUBSTITUTE($A$4:$A$20,"-","") < SUBSTITUTE($A3,"-","")))

Thanks for the response, but this isn't what I'm looking for. There should only be 8 numbers that are less than A3 but the answer I am getting with your formula is 13. That is because the blanks are still being counted. There are 5 blanks in that range. 8+5=13. I need to eliminate the blank lines which is what I was trying to do with this part of my formula "NOT(ISBLANK($A$4:$A$20))" (actually counting non-blank cells). If there was a way to multiply the two that would eliminate the cells I don't want.


EDIT: I thought I had a solution but for some crazy reason the formula is evaluating A3 (90100000012) as being larger than A17:A20 which it is not. =SUMPRODUCT(--(SUBSTITUTE($A3,"-","")>(SUBSTITUTE($A$17:$A$20,"-","")*NOT(ISBLANK($A$17:$A$20)))))>0 (narrowed the range done just to those items that should be greater than A3 for testing purposes).

EDIT 2: I made this way too complicated. Just needed to do this. =SUMPRODUCT(($A3>($A$4:$A$20))-ISBLANK($A$4:$A$20))>0