Summing the results of the VLOOKUP command

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the vlookup command several times to return values found in another worksheet. I would like to SUM the results of the vlookup command but since the values are not constants the SUM funtion is returning an error. How can I sum the results of the VLOOKUP command?
 
You should be able to sum (numeric) results of formulas, no matter what
formulas.
What are the results of your lookups? What does your SUM formula look like?
What result did you expect? What did you get?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

James said:
I am using the vlookup command several times to return values found in
Click to expand...
another worksheet. I would like to SUM the results of the vlookup command
but since the values are not constants the SUM funtion is returning an
error. How can I sum the results of the VLOOKUP command?
 
Niek
The vlookup comand is in Cell B2:B7 and reads as follows
=vlookup(a2,earningls.xls!money,2)

I would like Cell B8 to sum the values in B2:B7. When I use the formula =sum(b2:b7) the result gives me a zero (0
when I give the formula =b2+b3 for testing I get the error <#value

I know this should work because I have done it before so I am not sure why it is not working now.
 
The results of my vlookup comannd in cells B2:B7 are as follows
B2 $2,719,754
B3 $2,350,630
B4 $2,350,630
B5 $2,350,630
B6 $1,324,646
B7 $2,350,630
B8 =SUM(B2:B7) returns a value of zero (0)
 
James said:
Niek,
The vlookup comand is in Cell B2:B7 and reads as follows:
=vlookup(a2,earningls.xls!money,2)

I would like Cell B8 to sum the values in B2:B7. When I use the formula
Click to expand...
=sum(b2:b7) the result gives me a zero (0)
when I give the formula =b2+b3 for testing I get the error <#value>

I know this should work because I have done it before so I am not sure why
Click to expand...
it is not working now.

The most likely thing is that the values returned by your VLOOKUP formulas
are not numbers but text. Of course, this is because you have text in the
range that is being used by the VLOOKUP. It also means that you must have
text in A2, whose value you are looking up, in order to get a match. You
must understand that a number (say 1234.56) formatted as currency ($) and so
displaying as $1,234.56 is quite different from a text string that consists
of the nine characters between these quotes: "$1,234.56". To Excel, the
latter is much more similar to "abcdefghi" than to the number 1234.56.

You need to correct your data, etc., so that you are dealing with numbers,
not text.
 
Maybe you can coax your data into becoming true numbers.

Try adding two minus signs to the front of your Vlookup formula:

=--VLOOKUP(A2,earningls.xls!money,2)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

The results of my vlookup comannd in cells B2:B7 are as follows
B2 $2,719,754
B3 $2,350,630
B4 $2,350,630
B5 $2,350,630
B6 $1,324,646
B7 $2,350,630
B8 =SUM(B2:B7) returns a value of zero (0)
 
Back
Top