Count how many substrings are contained within a superstring

Question

Counting the number of superstrings within a range contain a substring is easy in LibreOffice Calc and Excel. How to count how many times a superstring contains substrings within a range? In the example below, the times an Animal is contained in a set is COUNTIF($A$2:$A$5,""&B2&"") for Dog, COUNTIF($A$2:$A$5,""&B3&"") for Cat and so on. How do I count how many Animals of range B:B are contained in "Cat Dog Mouse Snake"? The answers on column D should be 3, 1, 1, 1.

You could probably adapt the approach in [this answer](https://stackoverflow.com/questions/53109551/is-there-an-excel-formula-that-will-search-a-list-and-highlight-text-containing), though I haven't tested it in LibreOffice Calc. — BigBen, Apr 27 '20 at 19:46

score 1 · Answer 1 · answered Apr 27 '20 at 20:06

Place the following formula in D2. It is an array formula so you will need to use Control+Shift+Enter instead of just Enter. You will know you have done it right when { } show up around the formula in the formula bar. Note these cannot be added manually.

=SUM(--NOT(ISERR(SEARCH($B$2:$B$5,A2))))

Copy the cell and then paste in D3:D5.

Gary's Student · Answer 2 · 2020-04-27T20:30:07.210

0

First put this UDF in a standard module:

Public Function KeyKount(s As String, keywds As Range) As Long
    Dim temp As String, cell As Range

    KeyKount = 0
    temp = " " & s & " "
    For Each cell In keywds
        v = " " & cell.Text & " "
        If InStr(temp, v) > 0 Then KeyKount = KeyKount + 1
    Next cell
End Function

Then in a cell enter, for example:

=keykount(A2,B2:B10)

edited Apr 27 '20 at 20:30

answered Apr 27 '20 at 20:11

Gary's Student

95,722
10
59
99

1

@BigBen The previous was junk......................I have revised. – Gary's Student Apr 27 '20 at 20:30
you just needed to leave off the `>0` part: `=SUMPRODUCT(--ISNUMBER(SEARCH($B$2:$B$10,A2)))` – Scott Craner Apr 27 '20 at 20:37
@ScottCraner I did not like my first answer, I was getting extra counts if one of my substrings was part of another substring. This way I padded each substring with spaces. – Gary's Student Apr 27 '20 at 20:41
Fair enough: `=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$B$2:$B$10&" "," "&A2&" ")))` – Scott Craner Apr 27 '20 at 20:53

score 0 · Answer 3 · answered Apr 27 '20 at 20:58

Either:

=SUMPRODUCT(--(ISNUMBER(MATCH(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"),B:B,0))))

Or

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$B$2:$B$5&" "," "&A2&" ")))

Again both work in Excel, not sure about librecalc. The first will do fewer interactions if the string has less words than the lookup list, while the second is the opposite

score 0 · Answer 4 · answered Apr 28 '20 at 18:47

0

For LO Calc:

=SUMPRODUCT(ISNUMBER(SEARCH(B$2:B$5;A2)))

answered Apr 28 '20 at 18:47

Jim K

12,824
2
22
51

Count how many substrings are contained within a superstring

4 Answers4