Countifs

wthalstead

Hi,

I have been searching the boards and am not coming up with what I want so I am hoping someone can point me in the right direction.

I have a sheet with a multi-select column. In the column you can select:

A

B

A, B

I want to find the 3 formulas that will count the number of times A shows up by itself, the number of times B shows up by itself, and the number of times that A, B show up together. Every time I try it keeps counting all of the times, for example, A shows up whether it be by itself or with another.

Thank you so much!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327351#Comment_327351

Can you share the exact formula you're using? Mine works fine, so you likely either have a syntax error or a data issue.

Jeff Reisman

@wthalstead Try these. Replace "Column:Column" with the column name range you're evaluating.

=COUNTIF(Column:Column, "A")

=COUNTIF(Column:Column, "B")

=COUNTIF(Column:Column, AND(HAS(@cell, "A"), HAS(@cell, "B")))

When it comes to multi-select cells, looking for a single value will only match if the entire cell matches that value. Using HAS allows the formula to look for specific whole values in a multi-select cell. For example, if the cell has values of Hello and Goodbye, looking for a cell equal to "Hello" will not match that cell, but looking for one that HAS(@cell, "Hello") will match.

Just a note - it's always helpful to include the formulas you have tried so far.

wthalstead

Thanks Jeff. The first two worked great, but the third came back as #UNPARSEABLE.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327351#Comment_327351

Can you share the exact formula you're using? Mine works fine, so you likely either have a syntax error or a data issue.

wthalstead

I am all set. You were right - it was a syntax. I had the @Cell capitalized so it didn't like it. I really appreciate your help!