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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!