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 "~"
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!
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 ~.
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... :(
-------------------------------------------------------------------------
No comments:
Post a Comment