CONTAINS in COUNTIF referencing to another sheet returns #UNPARSABLE.

heyjay

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

Jeff Reisman

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