Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kytihu
 
Posts: n/a
Default COUNTIF not counting properly!! Character limit?


I have a list of cells with text. I want to count how many times each
unique text cell appears. However, countif is having trouble
accurately distinguishing between unique cells

Ex:
ALLSTATE INSURANCE CO.VAN??
ALLSTATE INSURANCE CO.VANIL

If I were to ask it how many times ALLSTATE INSURANCE CO.VAN?? appeared
in this range of two cells, it would tell me 2, although the correct
answer is obviously 1. Is there a character limit for countif? Why is
it missing this simply calculation?


--
kytihu
------------------------------------------------------------------------
kytihu's Profile: http://www.excelforum.com/member.php...o&userid=30971
View this thread: http://www.excelforum.com/showthread...hreadid=506388

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default COUNTIF not counting properly!! Character limit?


If you are trying to match a literal question mark (which is a wildcard
for any single character) you need to prepend a tilde (~) to it.

=COUNTIF(E7:E8,"ALLSTATE INSURANCE CO.VAN~?~?")

Otherwise, without the tildes,
=COUNTIF(E7:E8,"ALLSTATE INSURANCE CO.VAN??")
will match on ALLSTATE INSURANCE CO.VAN followed by any 2 characters.


Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=506388

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kytihu
 
Posts: n/a
Default COUNTIF not counting properly!! Character limit?


That's exactly it. It was reading the ?? as "any 2 letters" i guess.

Thanks!


--
kytihu
------------------------------------------------------------------------
kytihu's Profile: http://www.excelforum.com/member.php...o&userid=30971
View this thread: http://www.excelforum.com/showthread...hreadid=506388

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
character limit in hyperlink function Brad B. Excel Worksheet Functions 0 December 23rd 05 12:04 AM
How can I change 255 character limit in 'hyperlink' function? Brad B. Excel Worksheet Functions 0 December 21st 05 10:03 PM
Character limit in an Excel Row Denise Excel Worksheet Functions 1 October 6th 05 02:33 AM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 03:17 PM
Cell 255 character limit and formula EaglesNest Excel Worksheet Functions 6 February 27th 05 01:06 AM


All times are GMT +1. The time now is 03:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"