COUNTIFS Unparseable

I have two formulas that are currently returning correct values, but I want to combine them and only return the count if both criteria are fulfilled:

=COUNTIF({Approved Avalara Fixes - Form Required Range 1}, "Name")

=COUNTIF({Approved Avalara Fixes - Form Required Range 7}, NOT(@cell = "Child"))

Above are the two formulas I need to combine into one COUNTIFS:

=COUNTIFS({Approved Avalara Fixes - Form Required Range 1}, "Name", ({Approved Avalara Fixes - Form Required Range 7}, NOT(@cell = "Child")))

However, the combined formula returns "Unparseable". How can I combine these two formulas?

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Misty,

    It looks like you may have an extra set of parenthesis. Try this:

    =COUNTIFS({Approved Avalara Fixes - Form Required Range 1}, "Name", {Approved Avalara Fixes - Form Required Range 7}, NOT(@cell = "Child"))


    Let me know if it works!


    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Misty,

    It looks like you may have an extra set of parenthesis. Try this:

    =COUNTIFS({Approved Avalara Fixes - Form Required Range 1}, "Name", {Approved Avalara Fixes - Form Required Range 7}, NOT(@cell = "Child"))


    Let me know if it works!


    Best,

    Heather

  • Thanks Heather. That corrected the Unparseable error, but something is still off because it isn't returning the correct value. When I filter in the main spreadsheet, it returns 9 rows that fulfil those two requirements. The formula is returning 0.

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Misty,


    Just curious - when you filter the main spreadsheet, are you using "contains" as the filter, or "is equal to"? If the cells contain "Name" or "Child" but also contain other characters (in other words, are not an exact match), the countifs won't work.



    Thanks,

    Heather

  • I am using similar filters in the main spreadsheet. The first filter is using "is equal to" "Name", the second filter is using "is not equal to" "Child". This returns 9 results.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!