getting column subtotals to ignore n/a

[stevp51]

I have a spreadsheet that is using lookup tables to auto-fill data
when you enter a part number. Until the part number is entered, the
lookup cells show N/A. Only problem is, I have Total cells at the
base of each column for costs and such, and it won't show a total with
any N/A's in the column. Is there any way to get that Total cell to
ignore N/A and show the running total?

Thanks all.

Steve

 

[JE McGimpsey]

I have a spreadsheet that is using lookup tables to auto-fill data
when you enter a part number. Until the part number is entered, the
lookup cells show N/A. Only problem is, I have Total cells at the
base of each column for costs and such, and it won't show a total with
any N/A's in the column. Is there any way to get that Total cell to
ignore N/A and show the running total?

A better solution is to avoid the N/As:

For instance, instead of

=VLOOKUP(A1,J:K,2,FALSE)

use

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

 

[stevp51]

A better solution is to avoid the N/As:

For instance, instead of

=VLOOKUP(A1,J:K,2,FALSE)

use

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

Ah. I'll research that. I'm new to the coding part of Excel. What is
ISNA?

 

[stevp51]

Ok, I was able to eliminate the N/A with the ISNA function, but its
not displaying the correct value either. It just remains blank.
Which part of the function says to be blank if N/A but display the
result when there is one?

thanks for the hand holding.

Steve

 

[JE McGimpsey]

Ah. I'll research that. I'm new to the coding part of Excel. What is
ISNA?

ISNA() is a built-in XL function that returns TRUE if its argument is
the #N/A error, and FALSE if it's not.

 

[JE McGimpsey]

Ok, I was able to eliminate the N/A with the ISNA function, but its
not displaying the correct value either. It just remains blank.
Which part of the function says to be blank if N/A but display the
result when there is one?

The syntax for IF() is

=IF(<condition>, <true result>, <false result>)

So for

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

if

ISNA(MATCH(A1,J:J,FALSE))

returns #N/A, then ISNA is true and the true result (the null string "")
is returned.

If a match is found then ISNA(MATCH(...)) is FALSE and the false result,
or

VLOOKUP(A1,J:K,2,FALSE)

is returned.