COUNTIFS Formula For Total Number Of Assigned Projects To Individual

JayRaditch · 04/28/20

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)

Genevieve P. · 04/29/20

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

Genevieve P. · 04/29/20

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)

Genevieve P. · 04/29/20