#Value error

[mrvegas]

OK, here's what I have ....

A1: $15,000
B1: formula with a vlookup that has "isna" built in because it looks up cells that don't return a match
C1: formula that adds A1 and B1 together

So when I try to add up column A and column B, I get #value! when the vlookup results in #NA#

Grrrrrrr.

 

[Aladin Akyurek]

OK, here's what I have ....

A1: $15,000
B1: formula with a vlookup that has "isna" built in because it looks up cells that don't return a match
C1: formula that adds A1 and B1 together

So when I try to add up column A and column B, I get #value! when the vlookup results in #NA#

Grrrrrrr.

Try one of...

=SUM(SUMIF(A1:B1,{"<0",">0"}))

=SUMIF(A1:B1,"<"&9.99E+307)

 

[Jonmo1]

Assuming your vlookup is something like

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

And your formula in C1 that adds A1 and B1 is something like
=A1+B1

That won't work on a cell containing "" formula result.

Change it to
=SUM(A1:B1)
or
=SUM(A1,B1)

Hope that helps.

 

[mrvegas]

perfect, thanks!

 

[mrvegas]

is there a way to make this work if instead of doing a sum of column A and column B, I need to subtract column B from column A?

 

[Jonmo1]

Try

=ABS(SUM(-A1,B1))