Hi,

I'm searching for a subtotal formula, which can ignore errors.
For regular sums I use a formula like this: "{=SUM(IF(ISNUMBER
(Data),Data))},
which excludes errors.
Does anyone have an idea how I can apply this to a subtotal operation?

Regards
Werner

Exclel XP SP3
Win XP SP3

Could you consider Autofilter follow by Subtotal.
This will ignore the error value.

Does this help? pls click yes if this help

cheers

"Werner Rohrmoser" wrote:

Hi,

I'm searching for a subtotal formula, which can ignore errors.
For regular sums I use a formula like this: "{=SUM(IF(ISNUMBER
(Data),Data))},
which excludes errors.
Does anyone have an idea how I can apply this to a subtotal operation?

Regards
Werner

Exclel XP SP3
Win XP SP3

Try something like this:

The filtered (or unfiltered) range to sum is B6:B20.

=SUMPRODUCT(SUBTOTAL(2,OFFSET(B6:B20,ROW(B6:B20)-ROW(B6),0,1)),SUMIF(OFFSET(B6:B20,ROW(B6:B20)-ROW(B6),0,1),"<1E100"))

--
Biff
Microsoft Excel MVP


"Werner Rohrmoser" wrote in message
...
Hi,

I'm searching for a subtotal formula, which can ignore errors.
For regular sums I use a formula like this: "{=SUM(IF(ISNUMBER
(Data),Data))},
which excludes errors.
Does anyone have an idea how I can apply this to a subtotal operation?

Regards
Werner

Exclel XP SP3
Win XP SP3

Another alternative is this array formula :

=SUM(IF(ISNUMBER(Data),Data)*(SUBTOTAL(3,OFFSET(Da ta,ROW(Data)-MIN(ROW(Data)),,))))

press Ctl, Shift and Enter

Does this do what you want?
Pls click Yes if this help

cheers



"T. Valko" wrote:

Try something like this:

The filtered (or unfiltered) range to sum is B6:B20.

=SUMPRODUCT(SUBTOTAL(2,OFFSET(B6:B20,ROW(B6:B20)-ROW(B6),0,1)),SUMIF(OFFSET(B6:B20,ROW(B6:B20)-ROW(B6),0,1),"<1E100"))

--
Biff
Microsoft Excel MVP


"Werner Rohrmoser" wrote in message
...
Hi,

I'm searching for a subtotal formula, which can ignore errors.
For regular sums I use a formula like this: "{=SUM(IF(ISNUMBER
(Data),Data))},
which excludes errors.
Does anyone have an idea how I can apply this to a subtotal operation?

Regards
Werner

Exclel XP SP3
Win XP SP3

Thank you Biff, formula is working well.

Werner

Thank you xlmate,
I modified your formula like this (see at the end ",0,1"):
=SUM(IF(ISNUMBER(Data),Data)*(SUBTOTAL(3,OFFSET(Da ta,ROW(Data)-MIN(ROW
(Data*)),0,1))))
and now it works perfect.

Werner

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Werner Rohrmoser" wrote in message
...
Thank you Biff, formula is working well.

Werner

Thank for the feedback, cheers

"Werner Rohrmoser" wrote:

Thank you xlmate,
I modified your formula like this (see at the end ",0,1"):
=SUM(IF(ISNUMBER(Data),Data)*(SUBTOTAL(3,OFFSET(Da ta,ROW(Data)-MIN(ROW
(DataÂ*)),0,1))))
and now it works perfect.

Werner