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

S

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?
 
R

Ron@Buy

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

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.
 
P

Pecoflyer

sandy;247089 said:
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
 
S

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 ?
 
P

Pecoflyer

sandy;247981 said:
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
 
D

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.
 
D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top