Count Ifs Multiselect drop down

lcain

Good morning!

I am running into an issue with my formulas. I currently have a log sheet that records detailed information about jobs, and a metrics sheet (see above) that is used to tabulate certain information.

My metrics sheet information is broken down between internal/external design work that is active or has been completed in the last year. I am trying to get counts of each job type which is contained in a multiselect drop down in the log sheet.

My formula is coming back with "0" when I know there should be at least one in each of these categories. I have copied my formulas for each row below.

Concord - Internal Active Sprinkler: =COUNTIFS({Job Type}, CONTAINS("Fire Sprinkler", {Job Type}), {Engineering Completion Date}, "", {CADD Internal/External}, "Internal")

Concord - Internal Year-to-Date Sprinkler: =COUNTIFS({Job Type}, CONTAINS("Fire Sprinkler", {Job Type}), {Engineering Completion Date}, @cell >= TODAY(-365), {CADD Internal/External}, "Internal")

Concord - External Active Sprinkler: =COUNTIFS({Job Type}, CONTAINS("Fire Sprinkler", {Job Type}), {Engineering Completion Date}, "", {CADD Internal/External}, "External")

Concord - External Year-to-Date Sprinkler: =COUNTIFS({Job Type}, CONTAINS("Fire Sprinkler", {Job Type}), {Engineering Completion Date}, @cell >= TODAY(-365), {CADD Internal/External}, "External")

Any help is much appreciated!

Razetto

@lcain Hi, With dropdown menus I use HAS in the formula with great success.

https://help.smartsheet.com/function/has

bisaacs

Hey @lcain,

If you're only wanting to count cells that contain both those items, then you'll want to use COUNTIF, AND, CONTAINS in the formula:

=COUNTIF(Dropdown:Dropdown, AND(CONTAINS("Criteria 1", @cell), CONTAINS("Criteria 2", @cell)))

If you want to count cells that have either one of them, just change the AND to OR.

Hope this helps!

lcain

For example, I want to:

COUNT Job Type

WHEN Engineering Completion Date - is blank

AND Internal/External - is Internal