SUMPRODUCT to substiute array formula

[Jan Kronsell]

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

 

[Pete_UK]

Try this (normally entered):

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

Hope this helps.

Pete

 

[Mike H]

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]

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

Jan

 

[Jan Kronsell]

That worked, but can you tell me why?

Jan

Hi,

Try this

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

 

[Mike H]

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]

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

Jan

 

[Ashish Mathur]

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com