CONTAINS in COUNTIF referencing to another sheet returns #UNPARSABLE.

Options
heyjay
heyjay ✭✭✭✭✭
edited 03/07/22 in Smartsheet Basics

I want to count all the cells with the word "Exempt" in the Column/Reference {Stat}. Using the formula below gives me the #UNPARSABLE error.

=COUNTIFS({BIN}, [BIN #]@row, {Stat}, CONTAINS("Exempt",{Stat})

=COUNTIFS({BIN}, [BIN #]@row, {Stat}, CONTAINS("Exempt",{Stat}@cell)

=COUNTIFS({BIN}, [BIN #]@row, {Stat}, CONTAINS("Exempt",{Stat}@row)


However, this one works, but does not count the all varieties of Exempt items in the column:

=COUNTIFS({BIN}, [BIN #]@row, {Stat}, "Exempt Unit(s)")

...

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Since you're already supply the second criteria range ({Stat}), you don't need to list it again in the CONTAINS. Use @cell by itself. Also, you need a second end parentheses on the end of the formula, since you have to close off your CONTAINS function as well as the COUNTIFS formula.

    =COUNTIFS({BIN}, [BIN #]@row, {Stat}, CONTAINS("Exempt", @cell))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!