Subtotal formula to ignore N/As

**Tuneman** · 05-25-2010, 01:32 PM

So I have a filtered array that I want to calculate the sum of whenever I filter it. The problem is there are some N/As in the array. Is there any way around this problem?

I tried this:
=SUBTOTAL(3,IF(ISNUMBER(F7:F1123),F7:F1123,FALSE))

with ctrl+shift+enter

and it didnt't work. Any advice?

**shg** · 05-25-2010, 01:40 PM

Look at the function_num constants for SUBTOTAL in Help.

**day92** · 05-25-2010, 02:11 PM

Try this...

=IF(ISERROR(SUBTOTAL(3,IF(ISNUMBER(F7:F1123),F7:F1123,FALSE))),"",SUBTOTAL(3,IF(ISNUMBER(F7:F1123),F7:F1123,FALSE)))

**daddylonglegs** · 05-25-2010, 02:21 PM

You can't feed an array to SUBTOTAL in that way, try like this

=SUM(IF(ISNUMBER(F7:F1123),IF(SUBTOTAL(2,F7:F1123),F7:F1123)))

confirmed with CTRL+SHIFT+ENTER

**Tuneman** · 05-25-2010, 03:03 PM

ya but will that work with filters?

**daddylonglegs** · 05-25-2010, 03:13 PM

Actually, in hindsight, no, that won't work, sorry, you can't use SUBTOTAL like that either, you need an OFFSET function in there like this

=SUM(IF(SUBTOTAL(2,OFFSET(F7,ROW(F7:F1123)-ROW(F7),0)),F7:F1123))

confirmed with CTRL+SHIFT+ENTER

That will sum all of the visble numbers (after filtering) but ignore any errors, is that what you need?