Counting String Matches in a Range

google-sheets

Solution

You can use:

=COUNTIF(D3:D723,"*"&A1&"*")

It counts the amount of cells in D3:D7243 that have the substring in Cell A1. The * are there as a wildcard in front and behind the substring.

I made a working example for you as well to look at.

UPDATE: fixed statement

Problem

Currently I'm using this formula to count the number of string matches in a range: =COUNTA(FILTER(D3:D723,FIND(A1, D3:D723))) If `A1="am"` and range has `"ham"`, `"scammy"`, `"pan"`; then the cell will display `2`. It appears to work correctly, except for one thing- it displays a match of 1 if there are no matches. How can I fix this?

Original source