200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
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.
Thanks//Dev
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.