Counting with a condition disregarding errors

[vsoler]

Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A
and #DIV/0! basically).

I want to have in B1:B50 the count of the cells that are less than the
corresponding cell in A1:A50 using SUMPRODUCT and disregarding the
error values.

I have tried with (in B1):

=IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A
$50)))*($A$1:$A$50<A1)))

and copying down to B50.

However, I keep on getting #N/A as the result of my formula. The last
portion of the formula ($A$1:$A$50<A1) return #N/A that is not
overcome by the preceding ISERROR conditions.

Can anybody help?

 

[excelent]

=COUNTIF($A$1:$A$50,"<"&A1)


"vsoler" skrev:

 

[Teethless mama]

Try this:

=SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)*($A$1:$A$50<>""),1)))

ctrl+shift+enter, not just enter

 

[T. Valko]

No need for the second IF:

=SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<>"")))

Biff

 

[vsoler]

No need for the second IF:

=SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<>"")))

Biff







- Mostrar texto de la cita -

Dear T. Valko,

Your formula does not work in my Excel 2003. (A1:A50<A1) always
returns FALSE because a cell cannot be smaller than itself.
Can you propose a workaround?

Thank you

 

[David Biddulph]

Did you remember the control-shift-enter?

 

[T. Valko]

Dear T. Valko,

Your formula does not work in my Excel 2003. (A1:A50<A1) always
returns FALSE because a cell cannot be smaller than itself.
Can you propose a workaround?

Thank you

See this screencap:

http://img72.imageshack.us/img72/6408/screencapju3.jpg

Biff

 

[T. Valko]

See this screencap:

http://img72.imageshack.us/img72/6408/screencapju3.jpg

Biff

P.S.

Did you try "excelent's" formula? It works unless there are no numeric
values in the range.

I was just pointing out that TM's formula didn't need the added IF function.

The modified version I posted will still fail if A1 is an error. We may need
a better explanation of what you're wanting to do.

Biff