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)))
Best Answer
-
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)))
Answers
-
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)))
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!