Using COUNTIFS With Contacts, CONTAINS, and OR

robinw

I'm essentially trying to make a sheet summary formula for how many open cases someone in a contact list has at the moment. I've got it working when I'm looking at the contact list one way, but the users of this sheet have a bad habit of entering data so that their email address displays in the contact list rather than their First and Last Name. I'm trying to figure out how to account for both methods in this formula that is checking for the person as well as the absence of data in another field.

The simple formula for reading contacts one way:

=COUNTIFS([Buyer Assigned]:[Buyer Assigned], HAS(@cell, "FIRST LAST NAME"), [Sent to Accounts Payable]:[Sent to Accounts Payable], "")

My latest attempt at a formula to include multiple display configurations for the contact. this one comes back as INCORRECT ARGUMENT SET.

=COUNTIFS([Buyer Assigned]:[Buyer Assigned], CONTAINS("FIRST LAST NAME", @cell), CONTAINS("email@address"), [Sent to Accounts Payable]:[Sent to Accounts Payable], "")

robinw

Resolved it myself!

=COUNTIFS([Sent to Accounts Payable]:[Sent to Accounts Payable], "", [Buyer Assigned]:[Buyer Assigned], OR(@cell = "FIRST LAST NAME", @cell = "email@address.com"))

Andrée Starå

Hi @robinw

I hope you're well and safe!

Glad you got it working!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!