COUNTIF Question
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
Best Answer

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 colorcoding on the parentheses all match up.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

Assuming all your versions of Lisa Start with Lisa (and the difference is only ion what comes afterwards), you can do as following:
 In your source sheet Create a MultiSelect Column Called 'Lisa Removed'. Put in a Column Formula: =SUBSTITUTE(Names@row, CHAR(10) + "Lisa", "")
 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.

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 colorcoding on the parentheses all match up.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@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

Did you make the first column a Multi Select?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!