Count Ifs Multiselect drop down
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!
Answers
-
@lcain Hi, With dropdown menus I use HAS in the formula with great success.
-
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!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
For example, I want to:
COUNT Job Type
WHEN Engineering Completion Date - is blank
AND Internal/External - is Internal
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 358 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!