If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
how to count if cell "contains" a word
On Tue, 15 Jul 2008 03:16:16 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: Okay, I think this is where we are looking at the problem differently. I read into the OP's examples that his delimited text were **all** 4 characters long. For that interpretation, the commas cannot affect the search as there would be no way to get a false positive under that condition. Note this response of the OP to an effort of mine in which I posed that question: Unfortunately, not all the values are four characters. --ron |
#22
|
|||
|
|||
how to count if cell "contains" a word
Okay, I think this is where we are looking at the problem differently. I
read into the OP's examples that his delimited text were **all** 4 characters long. For that interpretation, the commas cannot affect the search as there would be no way to get a false positive under that condition. Note this response of the OP to an effort of mine in which I posed that question: Unfortunately, not all the values are four characters. I had not read that response (which he posted earlier in the day that Harlan responded to me), so I was still laboring under the impression I had formed from my first reading of the original post when I responded to Harlan (which I think is obvious from the arguments I used in my responses to Harlan). Thanks for pointing that out. Rick |
#23
|
|||
|
|||
how to count if cell "contains" a word
Given Ron's posting to my last message to you, please disregard my last
several postings. It appears you were aware of the OP's posting to Ron mentioning that the items were not restricted to 4-character each and, having not read that message, I was not. Rick "Rick Rothstein (MVP - VB)" wrote in message ... . . . The way I read the initial post, the OP was interested in finding the 4-character sequences without regard to the commas (I assumed they were list delimiters), . . . Yes, it's PRECISELY because they'd be delimiters that it's A BIG MISTAKE to discard them. . . . so it seemed to me that not factoring them into the search was the thing to do. . . . Duh. Delimiters are usually CRITICAL. They're what allow for distinguishing abcf a separate token sought from abcFUBAR a token not necessarily being sought. Okay, I think this is where we are looking at the problem differently. I read into the OP's examples that his delimited text were **all** 4 characters long. For that interpretation, the commas cannot affect the search as there would be no way to get a false positive under that condition. Regardless, your formula =SUM(COUNTIF(A1:A5,{"*abcd*abcf*","*abcf*abcd*"})) 1. does too much work - if order is unimportant, then it's sufficient to search for abcd and abcf, either as delimited tokens or simple substrings, it's unnecessary to search for one then the other and the other then the first; 2. is a bug in waiting - try your formula on the singe cell 0 ,abcx,abcd,abcf,abcd,xyz In a single cell, should this be counted as 1 or 2? I don't know... in re-reading the original posting I am not totally sure; however, I still lean to the OP wanting to count lines of occurrences as opposed to total occurrence. And, I would point out that the OP did seem satisfied with the results of my formula (actually, technically it isn't "mine" as I only raised a question regarding making a minor change to the formula RagDyer posted), so that may be an answer in itself. Compare this to the results of my formula, =COUNT(FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ","")) +FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ",""))) If you don't like it as an array formula, make it I have no problem with array formulas. You raise a good point, although using my interpretation of the OP's setup, I would consider this modification of your formula to answer his request... =COUNT(FIND("abcd",A1:A5)+FIND("abcf",A1:A5)) Rick |
Thread Tools | |
Display Modes | |
|
|