Not able to understand Countif function in my file(Book1 Attached).



I have some names in Column A and I want to count how many times a name occurs

When I apply "Countif" function in my excel sheet in column B, it gives output as 460, 438, 371 etc against some names. Though I could not find any duplicacy.

Please help me.




Selected Answer

That's a funny one! I didn't see this before.

What's happening is that the < sign leading the search criterium is interpreted as "smaller than". 460 is the number of entries which are smaller than "vanessa....". When comparing strings using the operators < or > you basically get alphabetical sorting, meaning A < B, etc.

To overcome this problem you might use this formula.

=COUNTIF($A$2:$A$480,"?" & MID(A14,2,100))

Here the first character is replaced by a wild card which could also be a character which doesn't exist. Therefore "<vanessa" and "vanessa" (but also "bvanessa") will be interpreted as matches. If this method is causing its own confusion you would need to create a helper column where you list the addresses with the leading < removed.



Thanks Variatus.
it really clarifies. I encountered this kind of problem first time
but you figured it out. Thanks a million.
Deva (rep: 4) Oct 27, '17 at 12:41 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login