COUNTIFS Formula For Total Number Of Assigned Projects To Individual
Hello,
I am trying to troubleshoot a formula to achieve the following outcome.
Outcome:
Count the TOTAL number of rows in which [Assigned To (In-House Designer)] is "Danielle Morrison" AND the [Request On Hold] checkbox in UNCHECKED.
Background Information:
The Assigned To (In-House Designer) column type is a Contact List that Allows Multiple Contacts In Cell.
4 of the rows is ONLY ASSIGNED to Danielle Morrison
1 of the rows is BOTH ASSIGNED to Danielle Morrison and Damu Grooves.
Total rows ASSIGNED TO Danielle Morrison = 5
(Database Name: Creative Request) -- SCREENSHOT:
(Database Name: Creative Request - Dashboard Information) -- SCREENSHOT:
Current Formula:
=COUNTIFS({Creative Request In-House Designer}, "Danielle Morrison", {Creative Request On Hold}, false)
Best Answers
-
Hi @JayRaditch
You're very close with your formula! Two things:
1 . Checkboxes in Formulas
To indicate if a checkbox is checked in a formula, this is noted as the number 1...therefore when searching if the checkbox is not checked, you want to use 0 instead of "false". (Click Here to read more.)
=COUNTIFS({Creative Request In-House Designer}, "Danielle Morrison", {Creative Request On Hold}, 0)
2 . Searching Multi-Select Columns
Secondly, if you're looking through multi-select columns to see if a certain name or value is present in a cell, you will want to use HAS to see if the cell HAS that value... just having the value in quotes as you do will not find any matches when there are other names also in the same cell. HAS is specifically built for Multi-Select columns.
I've used the @cell function as the range in HAS to indicate it should look at each cell in the previously mentioned range.
Final Formula:
=COUNTIFS({Creative Request In-House Designer}, HAS(@cell, "Danielle Morrison"), {Creative Request On Hold}, 0)
Now the count will include if Danielle is selected on her own or with others... and only if the checkbox is not checked. Let me know if you have any questions about this or if it worked for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Oh! To add on to this... I see that you have the contact listed in the sheet where you're building the formula.
If you wanted to make this a bit smoother and applicable to any contact, you could adjust the sheet where the formula is in to have the contacts listed in a Contact Type of column (perhaps called "Assigned To"? instead of your current primary column), then use [Assigned To]@row as your criteria for the person to look for, instead of needing to type out the name in each formula:
=COUNTIFS({Creative Request In-House Designer}, HAS(@cell, [Assigned To]@row), {Creative Request On Hold}, 0)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @JayRaditch
You're very close with your formula! Two things:
1 . Checkboxes in Formulas
To indicate if a checkbox is checked in a formula, this is noted as the number 1...therefore when searching if the checkbox is not checked, you want to use 0 instead of "false". (Click Here to read more.)
=COUNTIFS({Creative Request In-House Designer}, "Danielle Morrison", {Creative Request On Hold}, 0)
2 . Searching Multi-Select Columns
Secondly, if you're looking through multi-select columns to see if a certain name or value is present in a cell, you will want to use HAS to see if the cell HAS that value... just having the value in quotes as you do will not find any matches when there are other names also in the same cell. HAS is specifically built for Multi-Select columns.
I've used the @cell function as the range in HAS to indicate it should look at each cell in the previously mentioned range.
Final Formula:
=COUNTIFS({Creative Request In-House Designer}, HAS(@cell, "Danielle Morrison"), {Creative Request On Hold}, 0)
Now the count will include if Danielle is selected on her own or with others... and only if the checkbox is not checked. Let me know if you have any questions about this or if it worked for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Oh! To add on to this... I see that you have the contact listed in the sheet where you're building the formula.
If you wanted to make this a bit smoother and applicable to any contact, you could adjust the sheet where the formula is in to have the contacts listed in a Contact Type of column (perhaps called "Assigned To"? instead of your current primary column), then use [Assigned To]@row as your criteria for the person to look for, instead of needing to type out the name in each formula:
=COUNTIFS({Creative Request In-House Designer}, HAS(@cell, [Assigned To]@row), {Creative Request On Hold}, 0)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve,
Thank you so much for the feedback and guidance. Your formulas worked perfectly and the client was very pleased. I appreciate you taking the time to assist me with this dilemma!
-
Great! So glad to hear that it worked for you!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!