ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Counting with a condition disregarding errors (https://www.excelbanter.com/excel-worksheet-functions/143383-counting-condition-disregarding-errors.html)

vsoler
Counting with a condition disregarding errors
 

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
Counting with a condition disregarding errors
 
=COUNTIF($A$1:$A$50,"<"&A1)


"vsoler" skrev:


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?

Teethless mama
Counting with a condition disregarding errors
 
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


"vsoler" wrote:


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?

T. Valko
Counting with a condition disregarding errors
 
No need for the second IF:

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

Biff

"Teethless mama" wrote in message
...
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


"vsoler" wrote:


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?

vsoler
Counting with a condition disregarding errors
 
On 19 mayo, 20:05, "T. Valko" wrote:
No need for the second IF:

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

Biff

"Teethless mama" wrote in message

...



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

"vsoler" wrote:

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?- Ocultar texto de la cita -

- 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[_2_]
Counting with a condition disregarding errors
 
Did you remember the control-shift-enter?
--
David Biddulph

"vsoler" wrote in message
oups.com...

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?

On 19 mayo, 20:05, "T. Valko" wrote:
No need for the second IF:

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

Biff

"Teethless mama" wrote in
message

...



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

"vsoler" wrote:

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?- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko
Counting with a condition disregarding errors
 

"vsoler" wrote in message
oups.com...
On 19 mayo, 20:05, "T. Valko" wrote:
No need for the second IF:

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

Biff

"Teethless mama" wrote in
message

...



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

"vsoler" wrote:

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?- Ocultar texto de la cita -

- 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


See this screencap:

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

Biff



T. Valko
Counting with a condition disregarding errors
 

"T. Valko" wrote in message
...

"vsoler" wrote in message
oups.com...
On 19 mayo, 20:05, "T. Valko" wrote:
No need for the second IF:

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

Biff

"Teethless mama" wrote in
message

...



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

"vsoler" wrote:

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?- Ocultar texto de la cita -

- 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


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




All times are GMT +1. The time now is 01:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com