Subtotal excl errors

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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 said:
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
Click to expand...

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 said:
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.
Click to expand...
 
Back
Top