Subtotal ignoring Error Values

  • Thread starter Thread starter Werner Rohrmoser
  • Start date Start date
W

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
 
Could you consider Autofilter follow by Subtotal.
This will ignore the error value.

Does this help? pls click yes if this help

cheers
 
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"))
 
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
 
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
 
Back
Top