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

[sandy]

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?

 

[Ron@Buy]

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

 

[David Biddulph]

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.

 

[Pecoflyer]

I am trying to add cells that have been filled with the VLOOKU
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 (i
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,Points!$K$5:$L$28,2),""

--
Pecoflye

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

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

 

[sandy]

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 ?

 

[Ashish Mathur]

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

 

[Pecoflyer]

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 ?


The Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.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

 

[Dave Peterson]

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.

 

[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)