Count Ifs with multi-select

MelissaTrinh

Hello I'm trying to do a count if with a multi-select column and am using the find formula. It is referencing another sheet. can you please let me know if I have any errors here? I get #Invalid Data Type.

Heather Duff

Hi @MelissaTrinh ,

Without doing a super deep dive on this - try replacing the second {Products 2} (within the FIND segment) with @cell. Since the first {products 2} is saying to look within that range, having it again within the FIND function is redundant.

Let me know if that fixes it.

Best,

Heather

MelissaTrinh

Hi there,

Thank you for answering my question. I tried using @cell and I still get the same error.This is what you meant, right?

Heather Duff

Make sure the formula reads "COUNTIFS"....hopefully that will fix it!

Heather Duff

Oh! In addition to switching to COUNTIFS, try using CONTAINS in place of FIND.

MelissaTrinh

I am getting closer for sure!

It actually returned a value with Contains but not the right value. It should return 3 instead of 8.

returned:

expected : 3 ( see count of "ABC")

Heather Duff

Oh - maybe HAS is a better option than CONTAINS. CONTAINS is going to count any cell that contains the text ABC, even if it's ABC News/OTV. HAS has better functionality in multi-select columns (my apologies for not noticing that!): https://help.smartsheet.com/function/has

MelissaTrinh

this worked! thank you :)

Heather Duff

Great! Glad it worked.