Posted to microsoft.public.excel.worksheet.functions
|
|
#VALUE! error when trying to add cells (VLOOKUP)
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
|