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

Tags:

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 03/04/24 Answer ✓

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

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 03/04/24 Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!