Formula
Hi
I need a formula to count a text column but I need to exclude a couple of texts, my formula seems to a bit clunky
I am getting a #UNPARSEABLE error
=COUNTIF ([10/06/24]3:[10/06/24]548, "<>AL - ANNUAL LEAVE", "<>PH - PUBLIC HOLIDAY", "<>RDO - ROSTERED DAY OFF", "<>WEEKEND", "<>CL - CARERS LEAVE", "<>SL - SICK LEAVE", "<>UL - UNPAID LEAVE")
Answers
-
Hello @Susan van Niekerk,
First, you need to remove your operator (<>) from the quotes. Anything in quotes is going to be considered as a string to evaluate.
I assume you are truing to count all the values that do not equal any of the various strings in quotes you have. If this is correct, you need to add an OR() function with a statement for each value you want to exclude from the count.
Try the below
=COUNTIF ([10/06/24]3:[10/06/24]548, OR(@cell <> "AL - ANNUAL LEAVE", @cell <> "PH - PUBLIC HOLIDAY", @cell <> "RDO - ROSTERED DAY OFF", @cell <> "WEEKEND", @cell <> "CL - CARERS LEAVE", @cell <> "SL - SICK LEAVE", @cell <> "UL - UNPAID LEAVE"))
Hope that helps!
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Hi Dan
I get a error #UNPARSEABLE
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 392 Global Discussions
- 213 Industry Talk
- 447 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!