Using COUNTIFS With Contacts, CONTAINS, and OR


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], "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!