# Contains, Find, or Has with Countifs

Options
✭✭✭✭✭
edited 06/20/22

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.

Paul

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
Options

Good morning @Genevieve Evans