COUNTIFS multiple sheets but search if CONTAINS assigned to name

Anne77

I am trying to count the number of tasks assigned to an employee across multiple sheets and if the task is not marked "complete". This formula works only if the employee is the only person assigned to a task. I want it to include any their name appears with multiple employees in the assigned to cell. (So if the cell "Contains" their name). I've tried multiple versions of adding CONTAINS(Primary 8) without luck

=COUNTIFS({Assigned To}, Primary8, {State}, <>"Complete") + COUNTIFS({Assigned To Range 2}, Primary8, {State Range 2}, <>"Complete") + COUNTIFS({Assigned To Range 3}, Primary8, {State Range 3}, <>"Complete") + COUNTIFS({Assigned To Range 4}, Primary8, {State Range 4}, +COUNTIFS({Assigned To Range 5}, Primary8, {State Range 5}, <>"Complete"))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/365349#Comment_365349

+ is correctly used there. My go to troubleshooting method is to use each formula separately first to make sure they both work. You could also have an issue if there's a cell in any of the ranges that is in an error state. Testing each formula separately will reveal that. You're not building these formulas in MS Word and copying them into Smartsheet, right? Because the quotes in MS Word are encoded in a way that doesn't work in Smartsheet.

Paul Newcome

@Jeff Reisman I try to avoid CONTAINS with contact type columns because that function never works for me with them. If I have to try to find "Anne" and ignore the last name, I have to use a FIND function.

Andrée Starå

Hi @Anne77

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Anne77

Not sure if this is helpful? I have 5 sheets of tasks. This is one. I need to count if "Anne" is contained in a cell, even if a task is assigned to multiple people and if the Status is not Complete. So the CountIfs should combine all 5 cells.

Right now my formula is only counting if "Anne" is the only person assigned to that cell.

If I change formula to this example, I get #incorrect: =COUNTIFS({Assigned To}, CONTAINS("Anne"), {State}, @cell <> "Complete")

Paul Newcome

Give this a try:

=COUNTIFS({Assigned To}, HAS(@cell, "Anne last_name"), {State}, @cell <> "Complete")

Anne77

If I start to add on the additional sheets to count, I get #unparseable

=COUNTIFS({Assigned To}, HAS(@cell, "Anne"), {State}, @cell <> "Complete") + COUNTIFS({Assigned To Range 3}, HAS(@cell, "Anne ), {State Range 4}, @cell <> "Complete)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/365346#Comment_365346

The reason that Paul specified the full name here: HAS(@cell, "Anne last_name") is because the HAS function is looking for a complete value within the multi-contact cell. If your values are "Anne Smith" and "Jeff Rogers," CONTAINS function will find 'Anne' or 'Jeff', but HAS needs the entire value, 'Anne Smith' or 'Jeff Rogers' in order to match that cell.

You also missed the closing quotes at the end after "Complete").

Anne77

Thanks Jeff! I just left the last name out on here for privacy. I did add the closing quote on "Complete" and still get #unparseable when adding multiple sheets. Is the "+" the correct way to add those?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/365349#Comment_365349

+ is correctly used there. My go to troubleshooting method is to use each formula separately first to make sure they both work. You could also have an issue if there's a cell in any of the ranges that is in an error state. Testing each formula separately will reveal that. You're not building these formulas in MS Word and copying them into Smartsheet, right? Because the quotes in MS Word are encoded in a way that doesn't work in Smartsheet.

Anne77

Thank you!! Trying them individually first worked! Then I was able to string them together

Paul Newcome

@Jeff Reisman I try to avoid CONTAINS with contact type columns because that function never works for me with them. If I have to try to find "Anne" and ignore the last name, I have to use a FIND function.