count specific text occurrences within a range

[ashillz]

My COUNTIF formula breaks down when looking for specific text within a range that includes text strings with values that partially match the string being searched. For example, counting the occurrences of "apple" incorrectly includes the count of "pineapple". Is there a better formula that can be used to correctly exclude the "pineapple" occurrences when counting the "apple" occurrences?

See the attached file for an example dataset on the "data" tab and my attempt at calculating the usage_count on the "summary" tab.

Thank you!

 

[bosco_yip]

Using Sumproduct instead of Countif, because Countif does not combined with another function, like CHAR function.


In B2, formula copied down:

=SUMPRODUCT(0+ISNUMBER(FIND(CHAR(10)&A2,CHAR(10)&data!B$2:B$16)))

 

[p45cal]

Power Query solution in green table in summary sheet (along with @bosco_yip 's solution). Both give the same result.
However, the PQ solution generates both columns. I've shown this by doctoring the source data in cell B6 by adding the keyword vegetable. So there's no need to generate your own list of keywords.
Also, I wondered whether, in the case where the same keyword appears more than once within a cell, whether that should be counted once or twice. I've illustrated the difference by having 2 instances of apple in cell B5 (maybe it can't happen?).

Should the source data change, you'll need to refresh the PQ table by right-cicking in it and choosing Refresh, like you would with a pivot table.

 

[Peter Bartholomew]

This is a 365 version of the @bosco_yip formula

= MAP(
    ¶ & keyvalue,
    LAMBDA(v, SUM(SIGN(ISNUMBER(SEARCH(v, ¶ & keywords)))))
  )
¶:=CHAR(10)

The final total could be evaluated using

= REDUCE(0, ¶&keyvalue,
    LAMBDA(a,v,
      a + SUM(SIGN(ISNUMBER(SEARCH(v, ¶&keywords))))
    )
  )

but it is unlikely that one would require the total without the breakdown.

If you prefer the messy workings don't show, one could define a Lambda function to recreate the COUNTIFS functionality

Worksheet formula
= CountIfsλ(keywords, keyvalue)

CountIfsλ
= LAMBDA(keywords, keyvalue,
    MAP(¶ & keyvalue & ¶,
      LAMBDA(v,
        SUM(SIGN(ISNUMBER(SEARCH(v, ¶ & keywords & ¶)))
      )
    )
  )