Countifs Formula with 4 ranges is giving me a zero when there are 2 items in the raw data that match

LTK4417

I am using the following formula and getting zero for my result. When I filter the raw data there are 2 items that meet these criteria. I cannot find the error.

=COUNTIFS({Current Performance}, CONTAINS([Primary Column]@row, @cell), {Job Family}, CONTAINS("Sales", @cell), {Month of Entry}, CONTAINS("Jan", @cell), {Year of Entry}, CONTAINS("2022", @cell))

Kelly Moore

Hey @LTK4417

The CONTAINS function is typically used with multiselect columns, or when looking to pull in a textstring that appears in multiple phrases (for example, CONTAINS("Dogs", @cell) would retrieve both "Shaggy Dogs" and "Cuddly Dogs"). Also, almost always quotes shouldn't be used with number values. Quotes confuse smartsheet into thinking the value is a textstring rather than a numeric value.

Is the {Job Family} column a multiselect column (multiple values are allowed in the same cell?). I left the CONTAINS there just in case it was multiselect.

Try this

=COUNTIFS({Current Performance}, [Primary Column]@row, {Job Family}, CONTAINS("Sales", @cell), {Month of Entry}, "Jan", {Year of Entry}, 2022)

Does this work for you?

Kelly

Kelly Moore

Hey @LTK4417

The CONTAINS function is typically used with multiselect columns, or when looking to pull in a textstring that appears in multiple phrases (for example, CONTAINS("Dogs", @cell) would retrieve both "Shaggy Dogs" and "Cuddly Dogs"). Also, almost always quotes shouldn't be used with number values. Quotes confuse smartsheet into thinking the value is a textstring rather than a numeric value.

Is the {Job Family} column a multiselect column (multiple values are allowed in the same cell?). I left the CONTAINS there just in case it was multiselect.

Try this

=COUNTIFS({Current Performance}, [Primary Column]@row, {Job Family}, CONTAINS("Sales", @cell), {Month of Entry}, "Jan", {Year of Entry}, 2022)

Does this work for you?

Kelly

LTK4417

Thank you, Kelly! That worked!