#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.
 
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 ?
 
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