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 color-coding 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 Multi-Select 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 color-coding 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!