Using countIfs with reference and multiple conditions

SachinTaneja

I am trying to create a summary view using values of 2 columns.

=COUNTIFS({Final Status}, "Pass With Remarks", {Internal Status}, CONTAINS("Fixed", {Internal Status}))

I want that if any cell in range Final Status has "Pass With Remarks" and any cell that has Fixed in range Internal status should be considered. The formula is not giving any error but I am getting 0 as count even if the data is as per the condition.

I am not sure 🤔 about the mistake I am making.

SachinTaneja

Hi @hollyconradsmith ,

Thanks for your response. I tried using both solutions and the one that worked was

=COUNTIFS({Final Status}, "Pass With Remarks", {Internal Status}, CONTAINS("Fixed", @cell))

If I use HAS then only those rows are considered where the Value was just FIXED. It is not considered when it is part of a sentence Partially Fixed - Rest dev. in progress

hollyconradsmith

Hi @SachinTaneja ,

Is your Internal Status column a multi-select dropdown maybe? You should use HAS instead of CONTAINS for multi-select.

Also try using @cell instead of repeating the column name. CONTAINS("Fixed", @cell)

SachinTaneja

Hi @hollyconradsmith ,

Thanks for your response. I tried using both solutions and the one that worked was

=COUNTIFS({Final Status}, "Pass With Remarks", {Internal Status}, CONTAINS("Fixed", @cell))

If I use HAS then only those rows are considered where the Value was just FIXED. It is not considered when it is part of a sentence Partially Fixed - Rest dev. in progress

SachinTaneja

HI @hollyconradsmith ,

Can you please explain the difference between using @cell or the range directly?

The formulas examples never mention using @cell.

hollyconradsmith

Hi @SachinTaneja,

Check out this article.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell