Error when Subtotal function inc #N/A

[Guest]

Hi all

I have a spreadsheet 40 columns by 1000 rows (ie quite large and already
slow) with vlookups, of which some return #N/A.

I am trying to use the Subtotal function to sum each of the columns after an
autofilter has been applied to the required rows

Does anyone know how I can get the subtotal function to work even where
there is #N/A values?

Note! I don't want to use an IF statement with the vlookup to remove the
#N/A in the first place is not really feasible as that would slow it down
even more.

Thanks in advance

 

[Frank Kabel]

Hi
IF statements would be the easiest solution though. Try the following
(not tested):
=SUBTOTAL(9,IF(ISNA(A1:A100),,A1:A100))

 

[Frank Kabel]

Hi
correction: This subtotal function won't help. Try the following type
of array formula (entered with CTRL+SHIFT+ENTER):
=SUM(IF((SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT("1:100"))-1,0)))*(ISNUMBER(A
1:A100)),A1:A100))

 

[Guest]

It works!!! What a legend, thanks for that ...