running sum needed with #N/A in array
 

hi

=SUMIF(B2:B15,"<#N/A")

Mike

On Thu, 22 Jan 2009 13:01:01 -0800, Lois
wrote:

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)

running sum needed with #N/A in array
 

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)
--
Thanks so much
Lois - unskilled user

running sum needed with #N/A in array
 

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...)
"Lois" wrote:

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)
--
Thanks so much
Lois - unskilled user

running sum needed with #N/A in array
 

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)),"",VLOOKU P(G4,$A$1:$F$31,2,FALSE))


Gord Dibben MS Excel MVP

On Thu, 22 Jan 2009 13:01:01 -0800, Lois
wrote:

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)

running sum needed with #N/A in array
 

This will ignore *all* errors:

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

--
Biff
Microsoft Excel MVP


"Lois" wrote in message
...
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)
--
Thanks so much
Lois - unskilled user