yogi_Count Instances Of Clips From Unique Entries Made Up By Concatenating Palylists!C2 Through D

                                          Google Spreadsheet   Post  #1496
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-25-2014
post by Simon LEFEUVRE (http://productforums.google.com/forum/?zx=tu8u6x37u114&usp=sheets_web#!mydiscussions/docs/vZ8Q0cF5ljc)
How to use COUNTIF with string containing "?" or "~"

Hi!

I'm using the function COUNTIF on a range of cells which contains text.

But some strings contains the characters "~" or "?" and the function COUNTIF seems to interpret these strings like wildcards and results are 0 (zeros)

Some example :

a cell (A1) contains the text "Spring ~A B °C~"

in an another cell (B2) I put the command =COUNTIF(A:A;A1) and it returns 0

It seems that the character "~" in the string is considered as a wildcard.

How can I fix this? 

---

Hello Yogi!

Thanks for your help!

So the spreadsheet is here :

https://docs.google.com/spreadsheets/d/1W_YqidRlT7eIV8cEtSE3Lw_B82MDa6zm_Ve89ZEsPyw/edit?usp=sharing

The datas are in the tab named "Playlists"

The column G is generated with concatenation of columns C and D for each line.

This colums is sorted to have only one item of each entry in the tab "Clips"

What I want to do :

In front of each entry in tab "Clips" I want to show the number of time it appears in "Playlists"

So I used the formula :

= COUNTIF(Playlists!$G$2:$G; $A2 )

to do this. But I remarked entries with "~" in the string have zero as result... :(

-------------------------------------------------------------------------

In regard to handling wild card characters ? and * and the character ~ as literal characters one has to precede them with another ~ in the search string. In Simon's data I only noticed use of ~ character only -- and that is why I have addressed handling ~ as a literal character in the solution here. In case there are ? and * that have to handled as literals these can also be handled in a manner I have addressed the issue of ~.