Contains, Find, or Has with Countifs

Paul G.

A team enters their consultations into a Smartsheet input sheet. This includes the type of consultation in the Type column. The Type column is a multiple select dropdown column so there can be more than one consultation type listed.

On the calculation sheet are the formulas to calculate the number of consultation types by agency and month. Currently the following formula used to calculate is =COUNTIFS({RngConsultBeginDate}, AND(IFERROR(MONTH(@cell), 0) = MONTH($[Selected Month]$2) - 3, IFERROR(YEAR(@cell), 0) = YEAR($[Selected Month]$2)), {RngConsultType}, $Type@row, {RngAgency}, $Agency@row). This formula provides a correct count when only one consultation type is listed. The calculation sheet feeds into a dashboard.

On the calculation sheet containing the formulas each consultation type appears in the Type column. My preference is to have the formula reference $Type@row so that I do not have to list each consultation type in each formula. In addition, if a new consultation type is added the formula can be pull down without the need to edit.

I have reviewed similar questions with suggested solutions of using CONTAINS, FIND, or HAS. I have tried all three but have not been successful in writing the formula correctly to provide an accurate count when there are two or more consultation types listed.

Thank you in advance for your responses and help.

Paul

Genevieve P.

Hi @Paul G.

Can I confirm that the 'Type' column in your current sheet with the formula has a single value, and it's only the Type column in the input sheet that's multi-select?

If so, you can use the HAS function! The structure would be like this:

=COUNTIFS({RngConsultBeginDate}, AND(IFERROR(MONTH(@cell), 0) = MONTH($[Selected Month]$2) - 3, IFERROR(YEAR(@cell), 0) = YEAR($[Selected Month]$2)), {RngConsultType}, HAS(@cell, $Type@row), {RngAgency}, $Agency@row)

Then you can pull the formula down and it will dynamically update as each row has a different Type that's referenced in the formula.

Cheers,

Genevieve

Genevieve P.

Hi @Paul G.

Can I confirm that the 'Type' column in your current sheet with the formula has a single value, and it's only the Type column in the input sheet that's multi-select?

If so, you can use the HAS function! The structure would be like this:

=COUNTIFS({RngConsultBeginDate}, AND(IFERROR(MONTH(@cell), 0) = MONTH($[Selected Month]$2) - 3, IFERROR(YEAR(@cell), 0) = YEAR($[Selected Month]$2)), {RngConsultType}, HAS(@cell, $Type@row), {RngAgency}, $Agency@row)

Then you can pull the formula down and it will dynamically update as each row has a different Type that's referenced in the formula.

Cheers,

Genevieve

Paul G.

Good morning @Genevieve Evans

Thank you, your formula worked great. I appreciate your help.

Paul G.