SUMPRODUCT to substiute array formula
 

I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))


entered as an array formula, to sum a column even if some of the cells
contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other functions, to
avoid the array formula?

Jan

SUMPRODUCT to substiute array formula
 

Try this (normally entered):

=SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100)

Hope this helps.

Pete

On Feb 26, 8:42*am, "Jan Kronsell"
wrote:
I have this formula

* * * =SUM(IF(ISERROR(A1:A100),0,A1:A100))

entered as an array formula, to sum a column even if some of the cells
contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other functions, to
avoid the array formula?

Jan

SUMPRODUCT to substiute array formula
 

Hi,

Try this

=SUMIF(A1:A100,"<1E100")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jan Kronsell" wrote:

I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))


entered as an array formula, to sum a column even if some of the cells
contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other functions, to
avoid the array formula?

Jan


.

SUMPRODUCT to substiute array formula
 

It did not. It returnes the error value it firtst meet in the column.

Jan

Pete_UK wrote:
Try this (normally entered):

=SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100)

Hope this helps.

Pete

On Feb 26, 8:42 am, "Jan Kronsell"
wrote:
I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))

entered as an array formula, to sum a column even if some of the
cells contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other
functions, to avoid the array formula?

Jan

SUMPRODUCT to substiute array formula
 

That worked, but can you tell me why?

Jan

Mike H wrote:
Hi,

Try this

=SUMIF(A1:A100,"<1E100")

I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))


entered as an array formula, to sum a column even if some of the
cells contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other
functions, to avoid the array formula?

Jan


.

SUMPRODUCT to substiute array formula
 

That worked, but can you tell me why?

Of course

1E100 is scientific notation for a very large number it represent 1 followed
by 100 zeroes. Now I guessed that your not dealing with numbers this large so
what it does is force the formula to sum omly numeric values less than this
very large number and the errors; by this criteria, are ignored.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jan Kronsell" wrote:

That worked, but can you tell me why?

Jan

Mike H wrote:
Hi,

Try this

=SUMIF(A1:A100,"<1E100")

I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))


entered as an array formula, to sum a column even if some of the
cells contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other
functions, to avoid the array formula?

Jan


.


.

SUMPRODUCT to substiute array formula
 

Thank you. I know about scientific notation, but did not know that using
this condition, will make Excel ignore the errors.

Jan

Mike H wrote:
That worked, but can you tell me why?

Of course

1E100 is scientific notation for a very large number it represent 1
followed by 100 zeroes. Now I guessed that your not dealing with
numbers this large so what it does is force the formula to sum omly
numeric values less than this very large number and the errors; by
this criteria, are ignored.

That worked, but can you tell me why?

Jan

Mike H wrote:
Hi,

Try this

=SUMIF(A1:A100,"<1E100")

I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))


entered as an array formula, to sum a column even if some of the
cells contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other
functions, to avoid the array formula?

Jan


.


.

SUMPRODUCT to substiute array formula
 

Hi,

Try this

=sumif(A1:A100,"=0")+sumif(A1:A100,"<=0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jan Kronsell" wrote in message
...
I have this formula

=SUM(IF(ISERROR(A1:A100),0,A1:A100))


entered as an array formula, to sum a column even if some of the cells
contains error values.

Now I wonder if it could be done using SUMPRODUCT or any other functions,
to avoid the array formula?

Jan