Count if Not Formula

Drea Mora

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

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

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

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