# COUNTIF Question

Options

Hello

We have a sheet that contains variations of words in a multi select column.

For example; Lisa, Lisa 1, Lisa 2.

I am trying to count how many times the word Lisa appears, regardless of the variation, i.e. Lisa 1 or Lisa 2.

I've tried lots of different formulas but nothing is working;

=COUNTM({Status Range 1}, ="Lisa", @cell)

=COUNTIF({Status Range 1}, CONTAINS("Lisa", @cell))

Can anyone help?

Many thanks

Lisa

Tags:

• ✭✭✭✭✭✭
Options

Check that all your parentheses, brackets, and commas are there. I copied that formula directly from a working cell in my test sheet and just replaced the ranges and values to match your example.

Be sure the color-coding on the parentheses all match up.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

the only way I've found to do this is to count the occurrences of each specific variation and add them together:

=SUM(COUNTIFS({Status Range 1}, HAS(@cell, "Lisa")), COUNTIFS({Status Range 1}, HAS(@cell, "Lisa 1")), COUNTIFS({Status Range 1}, HAS(@cell, "Lisa 2")))

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

@Jeff Davies , many thanks but unfortuntely it didnt work, getting back the #INCORRECT ARGUMENT SET error.

• ✭✭✭✭✭✭
Options

Assuming all your versions of Lisa Start with Lisa (and the difference is only ion what comes afterwards), you can do as following:

1. In your source sheet Create a Multi-Select Column Called 'Lisa Removed'. Put in a Column Formula: =SUBSTITUTE(Names@row, CHAR(10) + "Lisa", "")
2. In a second column put in the following formula: =COUNTM(Names@row) - COUNTM([Lisa Removed]@row)

The total sum of this column would give you the total qty of times the name Lisa shows up.

• ✭✭✭✭✭✭
Options

Check that all your parentheses, brackets, and commas are there. I copied that formula directly from a working cell in my test sheet and just replaced the ranges and values to match your example.

Be sure the color-coding on the parentheses all match up.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

@Leibel Shuchat , many thanks but it's not working. In the first column, it's removing multiples of 'Lisa and leaving the remaining wording. The second formula returns #UNPARESEABLE

• ✭✭✭✭✭✭
Options

@Lisa B 2022

Did you make the first column a Multi Select?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!