Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
Thank you Biff, formula is working well.
Werner |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Thank you Biff, formula is working well. Werner |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal ignoring Error Values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I require a macro to complete ignoring the following error | Excel Discussion (Misc queries) | |||
Sum ignoring cells with error | Excel Worksheet Functions | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions | |||
Ignoring error values in a formula | Excel Worksheet Functions | |||
Something similar to sum() but ignoring error value arguments! | Excel Worksheet Functions |