Count if Not Formula

I am capturing data from a form that collects data around car seat distribution and what county we distribute to. I want to capture when there is a date range for the date of distribution and the submitter is not from the following 5 counties: Haywood, Buncombe, Madison, Henderson and McDowell. I keep getting an #unparseable error message:

=COUNTIFS(County:County, NOT "Haywood", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS(County:County, NOT "Buncombe", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS(County:County, NOT "Madison", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS(County:County, NOT "Henderson", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30))) + COUNTIFS(County:County, NOT "McDowell", [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30)))

Find more posts tagged with

Formulas

Accepted answers

JamesB

@Drea Mora

You don't need the NOT argument in this instance. Try This...

=COUNTIFS(County:County, AND(@cell <> "Haywood", @cell <> "Buncombe", @cell <> "Madison", @cell <> "Henderson", @cell <> "McDowell"), [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), @cell <= DATE(2024, 9, 30)))

All comments

JamesB

@Drea Mora

You don't need the NOT argument in this instance. Try This...

Mark Safran

Hi @Drea Mora

If you want to use NOT( ) you'll need to modify what comes immediately after it to be a logical expression.

i.e. =COUNTIFS(County:County, NOT(@cell = "Haywood"), [Date Distributed]:[Date Distributed], AND(@cell...

Alternatively, you could probably just replace all of your "NOT" references in your current formula with <> which serve as the does-not-equal comparison. That formula would look like this:

=COUNTIFS(County:County, <> "Haywood", [Date Distributed]:[Date Distributed], AND(@cell...

-MCS

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.