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

Rafsun Royal

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å

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