Sum Calculation

[Mary L.]

Hi,
I would like to perform a sum on a column with several numbers filled in from a VLOOKUP command, and several "#N/A's" from that same VLOOKUP that appears because the VLOOKUP field on those cells is blank. When I sum the column I get #N/A and not the sum of just those several numbers which is what I want.
Please help - thank you in advance.

 

[J.E. McGimpsey]

One way:

Assume your VLOOKUP() functions are like:

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

Then replace them with

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

which will return the null string rather than #N/A if the lookup
value isn't found. SUM() will then ignore the text values.