CountIfs and Find logic-Can I use together to get pending task counts for a particular Person

Rafsun Royal · 11/01/18

I want to count Total number of Pending Task, Completed Task, Not Started for each of my team members from a different sheet (Master Daily Tasks Update-IT Dev Range) to a new sheet

Currently I am using this one to count total number of Pending Task: =COUNTIFS({Master Daily Tasks Update-IT Dev Range 3}, "Harry Porter", {Master Daily Tasks Update-IT Dev Range 4}, "Pending Task")

Ok, I am getting result for Harry Porter but there are some cells where I have multiple assignees and smartsheet is not counting those ones.

HELP Please- Need the best Possible Solutions!

Thanks in Advance!

Andrée Starå · 11/01/18

Hi Rafsun,

There is an issue with multiple contacts, and the Smartsheet team is working on a better solution. The text below is from another thread.

Thanks for the question. "Matching" functions like SUMIF, COUNTIF, IF, and VLOOKUP always look at the entire cell value, for consistency. If you want the function to do a "contains" search inside the cell, then you can use the FIND function with @cell, like this:

=SUMIF([Assigned To]:[Assigned To], FIND("Sally Smart", @cell) > 0, Duration:Duration)

Would that work until the new solution is in place?

I hope this helps you!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday · 11/01/18

I've had issues finding names in Smartsheets too. I found that using the email address worked better for me. But same formula as Andree shared.

Rafsun Royal · 11/01/18

Thanks Mike for your response!

Rafsun Royal · 11/01/18

You are AWESOME Andree! It Worked!

Andrée Starå · 11/01/18

Great!

Happy to help!

CountIfs and Find logic-Can I use together to get pending task counts for a particular Person

Comments

Categories