Countif and vlookup functions

[jaguar]

I am having a problem with countif recognizing the correct data. Fo
instance, I have COUNTIF(G:G ,"apple). It does not count everyone.
However, if I go back and retype the word apple in the cell, it the
recognizes it. I figure it is something to do with the format of th
cell, but I can't figure that out.

I am basically having the same problem with VLOOKUP. I have a fil
referring to another file looking for matching numbers. Some of th
numbers are not recognized until I retype them.

Thanks for any help

 

[Jason Morin]

You many have extraneous unseen characters in those cells.
You can either clean those cells up or use some wildcards
inside COUNTIF:

=COUNTIF(G:G,"*apple*")

HTH
Jason
Atlanta, GA

 

[Max]

I am basically having the same problem with VLOOKUP. I have a file
referring to another file looking for matching numbers. Some of the
numbers are not recognized until I retype them.

Think the "numbers" in the lookup col of the reference table
are text, rather than real numbers

One way is to adapt the VLOOKUP's lookup values ..

Try something like:

=VLOOKUP(TEXT(A1,"@"), ...)

where you convert the lookup numbers in col A
into text via using: .. TEXT(A1,"@") ...
so that it'll match the ones in the lookup col in the reference table

Or, if the problem is with the lookup numbers *in col A*
being text rather than numbers, then try something like:

=VLOOKUP(VALUE(A1), ...)
=VLOOKUP(A1+0, ...)
=VLOOKUP(A1*1, ...)

which will coerce Excel to recognize the text in col A as numbers

 

[Max]

I am having a problem with countif recognizing the correct data. For
instance, I have COUNTIF(G:G ,"apple). It does not count everyone.
However, if I go back and retype the word apple in the cell, it then
recognizes it. I figure it is something to do with the format of the
cell, but I can't figure that out.

--
As Jason pointed out, you probably have extraneous
leading or trailing spaces in the cells in col G
(these may not be apparent)

Another way to try to get the COUNTIF
is to use SUMPRODUCT

Try: =SUMPRODUCT(--(TRIM(G1:G100)="apple"))

Adjust the range G1:G100 to suit

Note that you can't use entire col references (e.g.: "G:G")
in SUMPRODUCT

 

[Stefano Gatto]

It has nothing to do with the display format, I think it's linked to some
wrong typing (type assignment) by Excel. I get the same problem when opening
..csv files. At this moment by assigning a type to each cell, Excel 2000 seems
to decide that those sequence of numbers are text rather than real numbers.
Then when using vlookup, it will discard those cells because of the type
mismatch!

I have been suggested in this case, to select the column and apply "Text to
columns" on it, in order to give another chance to Excel to reassign the
correct cell type... which Excel does. Give it a try.

PS: would be good that the user could decide the type of a cell when there
is choice...(e.g. when a number is inside)