Subtotal ignoring Error Values

[Werner Rohrmoser]

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

 

[xlmate]

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

Does this help? pls click yes if this help

cheers

 

[T. Valko]

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"))

 

[xlmate]

Another alternative is this array formula :

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

press Ctl, Shift and Enter

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

cheers

 

[Werner Rohrmoser]

Thank you Biff, formula is working well.

Werner

 

[Werner Rohrmoser]

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

Werner

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[xlmate]

Thank for the feedback, cheers