running sum needed with #N/A in array

[Lois]

I am trying to get a running sum from a list of VLookups where some of the
values have yet to be entered. Therefor some of the cells show as #N/A and
the sum formula SUM(B2:B15) is not working. How can I fix this. (just in case
not clear b2 is 5:47, B3 is 5:52 B4:b15 are currently #N/A but will change on
later dates)

 

[Sheeloo]

Type (or paste) this formula in the cell you want the result to be in
=SUM(IF(ISERROR(B2:B15),0,B2:B15))
and then press CTRL-SHIFT-ENTER

Ideally you should have your VLOOKUP wrapped in ISNA

=IF(ISNA(VLOOKUP...),"",VLOOKUP...)

 

[Gord Dibben]

Best method is to get rid of the #N/A from the cells by error-checking the
VLOOKUP formulas.

=IF(ISNA(vlookup formula)),"",vlookup formula))

Example of above...................

=IF(ISNA(VLOOKUP(G4,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G4,$A$1:$F$31,2,FALSE))


Gord Dibben MS Excel MVP

 

[T. Valko]

This will ignore *all* errors:

=SUMIF(B2:B15,"<1E100")