Prev: I must have clicked the wrong thing
Next: Summing across columns while skipping some columns
From: Mike H on 26 Feb 2010 07:11
> 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
> >>
> >>
> >> .
>
>
> .
>
From: Jan Kronsell on 26 Feb 2010 07:46
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
>>>>
>>>>
>>>> .
>>
>>
>> .


From: Ashish Mathur on 28 Feb 2010 09:19
Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
news:uvydU#rtKHA.2072(a)TK2MSFTNGP02.phx.gbl...
> 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
>
>
First  |  Prev  | 
Pages: 1 2
Prev: I must have clicked the wrong thing
Next: Summing across columns while skipping some columns