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
