Formula

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")

Find more posts tagged with

Accepted answers

All comments

SoS | Dan Palenchar

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!

Susan van Niekerk

Hi Dan

I get a error #UNPARSEABLE

Susan van Niekerk

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.