COUNTIFS multiple sheets but search if CONTAINS assigned to name
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"))
Best Answers
-
+ 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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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")
-
Give this a try:
=COUNTIFS({Assigned To}, HAS(@cell, "Anne last_name"), {State}, @cell <> "Complete")
-
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)
-
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").
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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?
-
+ 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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you!! Trying them individually first worked! Then I was able to string them together
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!