running sum needed with #N/A in array

L

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)
 
S

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...)
 
G

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
 
Top