Using COUNTIF in numbers to count cells that contain specific text but not only that text

Emily, Numbers allows you to use the “wildcard” * in COUNTIF.

So if you want to count the number of cells that contain “activity” then you simply can do something like:

=COUNTIF(data,”*activity*”)

SG

Further notes (adding separately as the forum software timed out):

If you want to count only cells with values that start with “activity” then you can use “activity*”.

If you want to count only cells with values that contain “activity” somewhere other than the beginning then you can use “*activity”.

The match is not case-sensitive. Whether you use activity or Activity the results will be the same.

SG

it will be easiest to create a set of columns that identify a particular string is found in the column of activities. The extra columns can identify that a particular string exists in the first string by using the following:

assuming the list of strings is in column A

and the extra columns are in column B thru D (assuming we are only looking for "Taekwondo", "Music", "Art")

the extra columns will indicate the presence of one of the words in the phrase does (or does not exist) by doing the following:

put the word (or phrase) you want to find in row 1.

make row 1 a header row

e.g. cell B1 contains "Taekwondo"

cell C1 contains "Music"

D1 contains "Art"

then select cell B2 and type (or copy and paste from her) the formula:

=IF(SUBSTITUTE($A2, B$1, "",occurrence)<>$A2, 1, 0)

shorthand for this is:

B2=IF(SUBSTITUTE($A2, B$1, "",occurrence)<>$A2, 1, 0)

select cell B2, copy

select cells B2 thru D8, paste

after you get this working, add a second header row:

and add the formula

B2=SUM(B)

select cell B2 copy

select cells B2 thru D2, paste

"If you want to count only cells with values that contain “activity” somewhere other than the beginning then you can use “*activity”.

Actually, "*activity" will count only cells with values that end with "activity", including those that contain only "activity"

Here's an example with the 'full set'. Each cell in columns B-F has the formula shown below the table, with the search string edited to match the label at the top of its column. The 'range' in each case is the single cell on the same row of column A as the formula.

The "*" wild card will accept 'any number of characters, including none'.

Column F uses the 'other' wild card, "?", which accepts (and requires) only 'a single character'. To count "practice" using ?, the search string "??act*" or "??act???" could be used.

Regards,

Barry

Wayne Contello wrote:

Oh Nice. I never noticed the wildcard!

It's an easy one to forget as I don't think it is that well documented. I was able to find this page. So wildcards are accepted not just in COUNTIF and SEARCH but also in COUNTIFS， AVERAGEIF, AVERAGEIFS and perhaps a few other functions as well.

SG

Oh Nice. I never noticed the wildcard!