Excel: How do I sum a row if one cell has #n/a in it?

C

candylj

I have created a spreadsheet using vlookups. If a cell is blank, the other
cell returns #n/a. However, a total may be in another cell. I am adding two
sets of information in another cell. If one cell has a number and other has
n/a, it returns n/a instead of the total number. The help screen for n/a
says use brackets (), but it does not make any sense. SUMIF, IF, COUNTIF
has not worked to get the End Total to add when there is a #n/a in the
sub-total fields.

Types Cost Amount Total Types Cost Total End
Total
Metro 2.50 1 2.50 n/a
2.50 **

** It returns #n/a instead of $2.50. What is the correct formula to by
pass the cell that shows n/a instead of a number?
 
H

Harlan Grove

candylj said:
I have created a spreadsheet using vlookups. If a cell is blank, the other
cell returns #n/a. However, a total may be in another cell. I am adding
two
sets of information in another cell. If one cell has a number and other
has
n/a, it returns n/a instead of the total number. The help screen for n/a
says use brackets (), but it does not make any sense. SUMIF, IF, COUNTIF
has not worked to get the End Total to add when there is a #n/a in the
sub-total fields.
Click to expand...
....

Better to fix the VLOOKUP formulas so that instead of

=VLOOKUP(a,d,c,0)

which could return #N/A, you use an IF test as well, like

=IF(ISNA(VLOOKUP(a,b,c,0)),"",VLOOKUP(a,b,c,0))

but next best would be using SUMIF to sum the range of these formulas.

=SUMIF(d,"<>#N/A")
 
Top