COUNTIF cell contains => 3 sub-strings compared to index cell

[rafaelh9]

I have an criteria cell that contains multiple stings text. I want to find how many times cells with SIMILAR text appear on an array.
The rule is:
IF a cell within selected range contains at least 3 strings that match strings in criteria cell
THANCount

Example:

Criteria Cell A1 contains text = Summer Infant - 2015 3D Lite Convenience Stroller

IFRange cell string includes = Summer, 2015, Stroller (or any other 3 sub strings that appear in criteria cell)
THAN Count

*Not that that criteria cell is content text than is changing therefore cannot use a defined text but rather a cell (i.e. cannot define in criteria "Summer Infant - 2015 3D Lite Convenience Stroller" BUT need to us A1)

[JohnTopley]

Try

=SUMPRODUCT(--ISNUMBER(SEARCH(D1:D3,A1)))

will return count of matches of data in D1:D3 which matches A1 text

[jason.b75]

You will need a helper column to do that.

Using B2:B10 as the range to be counted, enter the following formula into C2, and fill down to C10

=SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",LEN($A$1))),(ROW($A$1:INDEX($A:$A,LEN($A$1)-LEN(SUBSTITUTE($A$1," ",""))+1))-1)*LEN($A$1)+1,LEN($A$1))),B2)))

Then you can get your result from a formula like

=COUNTIF(C2:C10,">=3")

Note that the formula uses the space in the criteria to separate the sub strings, meaning that the single dash between Infant and 2015 will potentially be included as one of the 3 matching sub strings.

[JohnTopley]

Jason,
This will give the same result ..

=SUMPRODUCT(--ISNUMBER(SEARCH(Lookup_Rng,A2)))

Lookup_rng is the data to be counted using OFFSET as the cell count

=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B),1)

[jason.b75]

John, I think you misread part of the question, the criteria in A1 needs to be space delimited by the formula to use each word / substring in the cell as criteria.

[rafaelh9]

Thanks guys for your replies.
Jason - I tried your formula but didn't get the expected results
John - I didn't understand how to use your formula.

I've created a sample file (See attached) that shows what the formula gives me vs. expected results.
Will be grateful if you could review and revise it accordingly and than we could post the current formula for this request.

Tnx again

[FlameRetired]

Hi rafaelh9,

Welcome to the forum.

Try this

Please Login or Register  to view this content.

This formula is case sensitive.

[jason.b75]

How many cells do you have with criteria?

In your original question you said 'Criteria in A1' which is a single cell. The multiple criteria cells in your sample make all of the suggestions provided redundant. I'm not even sure that it will be possible with a formula.

FlameRetired's suggestion in post #7 will work with 2 substings in the criteria cells, but as you originally asked for 3 or more substrings, it will not work.

edit:-

Revising my formula to match your sample. Enter this into B2 and fill down.

=SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($C$2," ",REPT(" ",LEN($C$2))),(ROW($A$1:INDEX($A:$A,LEN($C$2)-LEN(SUBSTITUTE($C$2," ",""))+1))-1)*LEN($C$2)+1,LEN($C$2))),A2)))

The results of this formula are the count of substrings in C2 that appear in each cell in the Names column. You would then use COUNTIF to count the number of rows in column B that contan the specified number of sub strings.

The helper column can only work for one criteria cell, if you have 10 criteria cells then you would need 10 helper columns to make the formulas work.

[FlameRetired]

I tried 'fiddling' with variable 3 name criteria and mixing up the names list. This seems to work so far.

The weird thing is I can't account for the MIN other than that is what it takes to get correct returns. (Seems forced and kind of 'cheezy' IMPO).

Please Login or Register  to view this content.

Edit I went back and studied this in more depth. I can see the logic-connect now in MIN, and it's rather subtle.

[FlameRetired]

A small revision to my last formula.

=IF(C2="",0,MIN(INDEX(COUNTIF(A:A,"*"&TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1))-1)*99+1,99))&"*"),0)))

I forgot to account for blank cells in column C in my last attempt. Without that the formula returns 5s.

[jason.b75]

Rep added for that one, Dave

Although I can't help thinking that the sample and the question are not clear representations of each other.

The way I read the original question (or at least this line)

IF a cell within selected range contains at least 3 strings that match strings in criteria cell

Cells in the selected range (column A) in sample, could each contain any number of words, for example 8 to 10 words in each.
Criteria cell (C2) (implied as one cell in question, but 4 cells in sample) could contain, for example 5 words.
If a cell in column A contains 3 or more of the 5 words in C2, count as 1.

It's possible that I'm just overcomplicating the question by reading it how it was asked, but if I have read it correctly then I don't see any way it is possible with a single formula for each criteria cell.

I was working with

{=SUM(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($A$2:$A$5," ",REPT(" ",LEN($A$2:$A$5))),TRANSPOSE(ROW($A$1:INDEX($A:$A,MAX(LEN($A$2:$A$5)-LEN(SUBSTITUTE($A$2:$A$5," ",""))+1)))-1)*LEN($A$2:$A$5)+1,LEN($A$2:$A$5))),C2))*(LEN($A$2:$A$5)>0))}

which does what is needed up to the last evaluation step, but I couldn't devise a method of subtotalling the rows of the array, this was as far as I got before giving up and going for the helper column method, which is not compatible with the sample.

For the formula above to work in line with the original question, without helper columns, it would need to function as (pseudo formula)

{=SUM(SUBTOTAL(9,=--((ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($A$2:$A$5," ",REPT(" ",LEN($A$2:$A$5))),TRANSPOSE(ROW($A$1:INDEX($A:$A,MAX(LEN($A$2:$A$5)-LEN(SUBSTITUTE($A$2:$A$5," ",""))+1)))-1)*LEN($A$2:$A$5)+1,LEN($A$2:$A$5))),C2))*(LEN($A$2:$A$5)>0))>=3)))}

in order to validate the number of matching sub strings for each cell in column A, however, as we all know, SUBTOTAL doesn't accept anything other than a range for the second argument, and I don't know of any way to make the above work. I've tried a few things with AGGREGATE as well, but that is also array-unfriendly, at least with the function arguments that could potentially work.

I've aborted trying to make this work for now, no sense in trying to make it work if it's not what is needed, although I will probably go back to it later, might be a useful one to figure out a working method for future threads.

[FlameRetired]

@ Jason,

Thanks for the kind words, encouragement and rep.

I struggled with this one, too. It's pretty bad when I can't understand my own formula ... at first. (I had to come back and 'explain' it to myself.) LOL

Although I can't help thinking that the sample and the question are not clear representations of each other.

I would have to agree. I had to make some stuff up just to check, but that was based upon my own interpretation. Still not sure I am grasping the concept.

Would really like to see how it does with live data.

[jason.b75]

Something else that just came to mind.

Given the name used in the sample file, I wonder if this is cross-posted at MrExcel.

[rafaelh9]

Thanks a lot for your input, you're awsome!
[SOLVED] At the end I found the FuzzyLookup add-in to do the job.
Basically I set criteria list in one table and lookup list in the another, set similarity threshold at 0.75 and let it search for matches. I get pretty much the result I was aiming for. Added a Simple COUNTIF to the output and voila

[FlameRetired]

rafaelh9,

That's good to hear, and thanks for the feedback.