COUNTIFS + CONTAINS with a contact list

Paigevandegrift

Hello! I am trying to use a COUNTIFS formula with a CONTAINS formula for a list of contacts. The formula needs to count if a project is a certain phase, and if the assigned project lead is one of the designated contacts in the list of contacts I have. Can someone please provide an example of what this formula would look like? Everything I have tried had not worked, and I am not sure what the root problem is.

Paul Newcome

Try replacing the opening curly bracket before your name with quotes.

HAS(@cell, {Paige Van De Grift")

changes to

HAS(@cell, "Paige Van De Grift")

Paul Newcome

You would need to either incorporate an OR statement or include a column on the source sheet that has a column formula to output the team based on the lead.

=COUNTIFS({PHase}, "PTI", {Lead}, OR(HAS(@cell, "Tim"), HAS(@cell, "Jon"), HAS(@cell, "Jim")))

Paul Newcome

Happy to help. 👍️

Paul Newcome

The CONTAINS function does not work well with contact lists. Try using a HAS or FIND function instead.

Paigevandegrift

Paul, thanks for the info!

I am trying to use the HAS function but cannot figure out why it is giving me an error. For context, the first half of the formula works, but the {Project Lead}, HAS(@cell, {Paige Van De Grift") is where I am having problems. The project lead column is a contact column that is open to type in any names within the organization. Once I figure this part out, I will need to add 7 other contacts to the formula.

=COUNTIFS({Portal Project Phase}, [Primary Column]@row, {Project Lead}, HAS(@cell, {Paige Van De Grift"))

Please let me know if you have any suggestions!

Paul Newcome

Try replacing the opening curly bracket before your name with quotes.

HAS(@cell, {Paige Van De Grift")

changes to

HAS(@cell, "Paige Van De Grift")

Paigevandegrift

Thank you for catching that! Now that the formula works, how do you suggest adding the other contact names? The formula currently has two criteria, and I am trying to build on the second criterion (the name) to add more contacts, but I am unsure of how to do so.

The end goal would be to COUNTIFS it met the criteria for the project phase, and the criteria for the project lead name, which would match 1 out of 8 contacts. Not sure if an AND/IF statement would work here, or how to do so?

Paul Newcome

Are you able to provide some screenshots with sample data?

Paigevandegrift

I created a simple example that hopefully can help to explain what I am trying to achieve. I am focused on the Project Phase and Project Lead columns, where the phase column is a drop-down, and the lead column is a "contact list" where you can type anyone from the organization's name in.

For this example, I would want to count how many PTI projects are happening for the "team" (names in green), and not any PTI projects for anyone who isn't a part of the team" (red). So, theoretically, the PTI formula result would be "1", the POV formula result would be "1", and the Implement formula would also be "1", as there is one project going on for each phase that has a green team member as the project lead. Please let me know if you need any more information, and thank you in advance for your help!

Paul Newcome

You would need to either incorporate an OR statement or include a column on the source sheet that has a column formula to output the team based on the lead.

=COUNTIFS({PHase}, "PTI", {Lead}, OR(HAS(@cell, "Tim"), HAS(@cell, "Jon"), HAS(@cell, "Jim")))

Paigevandegrift

That worked, thank you so much for your help Paul!

Paul Newcome

Happy to help. 👍️

JTobin

I too am having trouble with doing a countifs when using a contact list and when a task is assigned to multiple individuals. I am trying to do a summary report where I have the names Bob, Johndoe, Janedoe, & Sue and then I want to have a column that counts how many tasks they are assigned.

Is there a way to do a count if it has a specific character string rather than using the full email address?

Paul Newcome

@JTobin You would need a text/number helper column on the source sheet and a basic cell reference type formula to bring the email addresses over into the helper column as a text string.

=[Assigned To]@row

Then you would be able to use the CONTAINS function on this helper column.