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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!