Can I count rows that contain an option from a multi-select dropdown?

Rach M

I have two tracker sheets: one for tax accounts, one for individuals that have POA authority. I want to be able to count the number of tax accounts that each POA is assigned to ONLY for accounts that have one of the tax types that they are approved for.

Is this possible?

Jason Tarpinian

You should be able to use COUNTIFS+HAS functions, HAS is very nice to use with multi-select as it looks for an exact string match, whereas CONTAINS will look for a match anywhere within a string.

=COUNTIFS([RANGE1]:[RANGE1], HAS(@cell, "value"), [RANGE2]:[RANGE2], HAS(@cell, "other value"))

Jason Tarpinian

You want to count if there are 2 matching values in the cell? This formula would count only if RANGE1 includes both "value1" and "value2"

=COUNTIFS([RANGE1]:[RANGE1], AND(HAS(@cell, "value1"), HAS(@cell, "value2")), [RANGE2]:[RANGE2], HAS(@cell, "other value"))

RDRGSJ00

Hello Rach M,
Please provide more details, or write out what you would like with the actual column names. For example:
"Give me the # [Tax accounts], For each [POA], WHERE [Type of Permissions] EQUALS "Admin"
From what I can gather, is this what you are trying to do? Where is the Tax Accounts column? Do you expect an actual count (e.g. 1, 2, 3) or the actual Account names?

Rach M

Sure!

What I want it to do, is give me the value in the first spreadsheet in the "# Non-payroll Tax Accounts Assigned" column.

"Give me the number of tax accounts (rows) in spreadsheet 2 where the POA column in spreadsheet 2 matches the name of the POA in this row of spreadsheet 1 and where the tax type in spreadsheet 2 is one of the approved tax types listed in this row of spreadsheet 1."

I could list out each of the approved tax types in the column, but that would be 10 options and I'm hoping to make this more sustainable, so if I add a tax type, I don't have to change the formula.

Jason Tarpinian

You should be able to use COUNTIFS+HAS functions, HAS is very nice to use with multi-select as it looks for an exact string match, whereas CONTAINS will look for a match anywhere within a string.

=COUNTIFS([RANGE1]:[RANGE1], HAS(@cell, "value"), [RANGE2]:[RANGE2], HAS(@cell, "other value"))

Rach M

Thanks @Jason Tarpinian! Do you know if there's a way to select the "value" and "other value" from the multi-select dropdown? Or do I have to natively identify each option directly within the formula?

Jason Tarpinian

You want to count if there are 2 matching values in the cell? This formula would count only if RANGE1 includes both "value1" and "value2"

=COUNTIFS([RANGE1]:[RANGE1], AND(HAS(@cell, "value1"), HAS(@cell, "value2")), [RANGE2]:[RANGE2], HAS(@cell, "other value"))

Rach M

Thank you @Jason Tarpinian!