Prev: I must have clicked the wrong thing
Next: Summing across columns while skipping some columns
From: Jan Kronsell on 26 Feb 2010 03:42
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: Pete_UK on 26 Feb 2010 04:29
Try this (normally entered):

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

Hope this helps.

Pete

On Feb 26, 8:42 am, "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk>
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

From: Mike H on 26 Feb 2010 06:00
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
>
>
> .
>
From: Jan Kronsell on 26 Feb 2010 06:17
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" <kronsell(nomorespam)@adslhome.dk>
> 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


From: Jan Kronsell on 26 Feb 2010 06:18
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
>>
>>
>> .


 |  Next  |  Last
Pages: 1 2
Prev: I must have clicked the wrong thing
Next: Summing across columns while skipping some columns