ARRAYFORMULA COUNT number of rows matching a source column where destination column CONTAINS source string in Google Sheets

Question

I have a table (input table) with some columns where one column has text.

I have another table (query table) with some query strings.

For each row in query table, I want to see how many rows in input table have the query string.

query table

query	count
alpha
bravo
charlie delta

input table

query	data
this is my alpha	one
I will bravo you	two
what is your name	three
how are you	four
she, charlie delta, said	five
one two charlie three alpha	six

The expected output would be this:

query	count
alpha	2
bravo	1
charlie delta	1

I tried something like this, but it does not print the expected result. It prints 2 for all rows. I assume this is because it's just counting the first row in query table (alpha).

=ARRAYFORMULA(
    IF(
        A2:A <> "",
        COUNTIF(
            REGEXMATCH(`input table`!A:A, A2:A),
            TRUE     
        ),
    )
)

Answer 1

You may try with the wildcard search:

=ARRAYFORMULA(
    IF(
        A2:A <> "",
        COUNTIF(
            'input table'!A:A, "*"&A2:A&"*"), 
        ))