CountM with multiple conditions

Adam1234

Hello,

I am working on a formula that takes information from another Smartsheet. The column I am taking information from happens to be a multiple drop-down box. I want to be able to take certain text from the drop down box with other criteria. Essentially I would like a Countifs that can account for a multiple drop box column type.

Paul Newcome

Are you able to provide some screenshots for reference or some sample data? Are you looking to COUNTM (the number of entries in a multi-select cell) or to COUNTIFS (count how many cells contain a specific text string)?

Adam1234

Hi @Paul Newcome, thank you for your response. I can not provide a Screenshot, but what I am looking for is the COUNTIFS option.

If there were a multiple drop down box column with "Pears", "Apples", and "Grapes"

And the cells in the column go as follows:

_____________

Apples, Pears

____________

Pears, Grapes

_____________

Grapes, Apples

______________

Apples

______________

If I wanted to know the number of Apples accounted for in the column (3 in this case), how will I go about that?

Paul Newcome

You would use something like this:

=COUNTIFS({Column To Count}, HAS(@cell, "Apples"))

Adam1234

Hi @Paul Newcome. Just trying to clarify, is the "@cell" referring to the same as the {Column To Count}?

Paul Newcome

You would write it exactly as is. @cell tells the HAS function to evaluate the previously established range on a cell by cell basis.

Chris C.

@Paul Newcome In this example, if you wanted to count multiple criteria such as "Apples", "Pears", and "Grapes", can you do that as well? I only see the option for one criteria in the formula.

Paul Newcome

@Chris C. You would incorporate an OR function like so:

=COUNTIFS({Column To Count}, OR(HAS(@cell, "Apples"), HAS(@cell, "Pears"), HAS(@cell, "Grapes")))

Chris C.

@Paul Newcome Awesome! Worked like a charm. Much appreciated my friend.

Paul Newcome

@Chris C. Happy to help. 👍️