#VALUE! error when trying to add cells (VLOOKUP)

I am trying to add cells that have been filled with the VLOOKUP function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2))," ",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it works if
there is a number there). What am I doing wrong?

Sandy try replacing " " with a zero in your VLOOKUP formula and see what
happens then

"sandy" wrote:

I am trying to add cells that have been filled with the VLOOKUP function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2))," ",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it works if
there is a number there). What am I doing wrong?

Apart from what you are doing to get the #VALUE! error, which others will
hopefully address, the other thing you have done wrong is to use the SUM
function but not told it what you want to add to F6+I6+L6+O6+R6+T6. You
haven't given it a second argument to the SUM function, so it isn't doing
anything useful for you. You may wish to look at Excel help for the SUM
function if you don't know what it is trying to do.

You may have intended either
=F6+I6+L6+O6+R6+T6
or
=SUM(F6,I6,L6,O6,R6,T6)

You will probably prefer the latter, as it ignores text entries such as the
" " string which you have requested from your IF function.
--
David Biddulph


"sandy" wrote in message
...
I am trying to add cells that have been filled with the VLOOKUP function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it works
if
there is a number there). What am I doing wrong?

sandy;247089 Wrote:
I am trying to add cells that have been filled with the VLOOKUP
function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it
works if
there is a number there). What am I doing wrong?

And to lighten the burden on the calculations try
=IF(countif(points!$k$5:$k$28,Q6),VLOOKUP(Q6,Point s!$K$5:$L$28,2),"")


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=68952

Thanks to everyone for your help.

Pecoflyer,

When I used your formula instead of mine I got the same results. Is it
better to use COUNTIF vs. ISNA ?

"Pecoflyer" wrote:


sandy;247089 Wrote:
I am trying to add cells that have been filled with the VLOOKUP
function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it
works if
there is a number there). What am I doing wrong?

And to lighten the burden on the calculations try
=IF(countif(points!$k$5:$k$28,Q6),VLOOKUP(Q6,Point s!$K$5:$L$28,2),"")


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=68952

Hi,

Try this

=IF(ISERROR(VLOOKUP(Q6,Points!$K$5:$L$28,2,0)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2,0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"sandy" wrote in message
...
I am trying to add cells that have been filled with the VLOOKUP function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it works
if
there is a number there). What am I doing wrong?

sandy;247981 Wrote:
Thanks to everyone for your help.

Pecoflyer,

When I used your formula instead of mine I got the same results. Is it
better to use COUNTIF vs. ISNA ?

"Pecoflyer" wrote:


sandy;247089 Wrote:
I am trying to add cells that have been filled with the VLOOKUP
function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error
(it
works if
there is a number there). What am I doing wrong?

And to lighten the burden on the calculations try

=IF(countif(points!$k$5:$k$28,Q6),VLOOKUP(Q6,Point s!$K$5:$L$28,2),"")


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster

------------------------------------------------------------------------
Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer'
(http://www.thecodecage.com/forumz/me...pecoflyer.html)
View this thread: '#VALUE! error when trying to add cells (VLOOKUP) -
The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=68952)



Well in this case there are two evaluations
first Vlookup, which can be heavy on calculations
then ISNA which evaluates the results

Countif only evaluates once and only returns TRUE or FALSE

So, if you have lots of Vlookups, Countif will be faster
If you don't, I think it doesn't matter


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=68952

I would use the =vlookup() or =match().

I figure that =countif() has to look at each cell in the range to see if it
should be included in the count.

If there's a match, then =vlookup() will quit as soon as it finds one.

But I've never done any extensive testing.

sandy wrote:

Thanks to everyone for your help.

Pecoflyer,

When I used your formula instead of mine I got the same results. Is it
better to use COUNTIF vs. ISNA ?

"Pecoflyer" wrote:


sandy;247089 Wrote:
I am trying to add cells that have been filled with the VLOOKUP
function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it
works if
there is a number there). What am I doing wrong?

And to lighten the burden on the calculations try
=IF(countif(points!$k$5:$k$28,Q6),VLOOKUP(Q6,Point s!$K$5:$L$28,2),"")


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=68952



--

Dave Peterson

Another difference...

=countif() treats numbers and text the same:

=countif(a:a,1)
and
=countif(a:a,"1")
will be the same.

=vlookup() and =match() will distinguish between a text 1 and a number 1 (="1"
and =1)



Dave Peterson wrote:

I would use the =vlookup() or =match().

I figure that =countif() has to look at each cell in the range to see if it
should be included in the count.

If there's a match, then =vlookup() will quit as soon as it finds one.

But I've never done any extensive testing.

sandy wrote:

Thanks to everyone for your help.

Pecoflyer,

When I used your formula instead of mine I got the same results. Is it
better to use COUNTIF vs. ISNA ?

"Pecoflyer" wrote:


sandy;247089 Wrote:
I am trying to add cells that have been filled with the VLOOKUP
function.

=SUM(F6+I6+L6+O6+R6+T6)

Those cells have a value from a VLOOKUP function

=IF(ISNA(VLOOKUP(Q6,Points!$K$5:$L$28,2)),"
",VLOOKUP(Q6,Points!$K$5:$L$28,2))

If there is nothing in one of the cells I get the #VALUE! error (it
works if
there is a number there). What am I doing wrong?

And to lighten the burden on the calculations try
=IF(countif(points!$k$5:$k$28,Q6),VLOOKUP(Q6,Point s!$K$5:$L$28,2),"")


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=68952



--

Dave Peterson

--

Dave Peterson