SUMPRODUCT to substiute array formula

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

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
 
Try this (normally entered):

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

Hope this helps.

Pete
 
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.
 
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.
 
Thank you. I know about scientific notation, but did not know that using
this condition, will make Excel ignore the errors.

Jan
 
Back
Top