Subtotal excl errors
 

Hi,

I have a filtered range of data which contains cells in columns that error
eg. #NA. =Subtotal(9,a1:a500) works fine if there are no errors and I'd
thought an array formula {=subtotal(9,if(isnumber(a1:a500),a1:a500))} would
have worked to return the sum of all values in the filtered list whereby
excluding the errors - but alas, this doesn't work.

Can this be done?

Thanks, Rob

Rob wrote:
Hi,

I have a filtered range of data which contains cells in columns that error
eg. #NA. =Subtotal(9,a1:a500) works fine if there are no errors and I'd
thought an array formula {=subtotal(9,if(isnumber(a1:a500),a1:a500))} would
have worked to return the sum of all values in the filtered list whereby
excluding the errors - but alas, this doesn't work.

Can this be done?

Thanks, Rob



It would much better to eliminate errors so that you can apply an
ordinary SubTotal formula, otherwise you need something like:

=SUM(IF(SUBTOTAL(2,OFFSET(A2:A500,ROW(A2:A500)-MIN(ROW(A2:A500)),,1)),A2:A500))

which must be confirmed with control+shift+enter instead of just with enter.

Make sure that you don't include the header row in the range the formula
is applied to.

Thanks Aladin, works well.

"Aladin Akyurek" wrote in message
...
Rob wrote:
Hi,

I have a filtered range of data which contains cells in columns that
error eg. #NA. =Subtotal(9,a1:a500) works fine if there are no errors
and I'd thought an array formula
{=subtotal(9,if(isnumber(a1:a500),a1:a500))} would have worked to return
the sum of all values in the filtered list whereby excluding the errors -
but alas, this doesn't work.

Can this be done?

Thanks, Rob

It would much better to eliminate errors so that you can apply an ordinary
SubTotal formula, otherwise you need something like:

=SUM(IF(SUBTOTAL(2,OFFSET(A2:A500,ROW(A2:A500)-MIN(ROW(A2:A500)),,1)),A2:A500))

which must be confirmed with control+shift+enter instead of just with
enter.

Make sure that you don't include the header row in the range the formula
is applied to.