# Counting with a condition disregarding errors

V

#### 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?

G

#### Guest

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

"vsoler" skrev:

G

#### Guest

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

#### T. Valko

No need for the second IF:

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

Biff

V

#### 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

D

#### David Biddulph

Did you remember the control-shift-enter?

T

#### T. Valko

T. Valko said:

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