Formula to Capture Active Projects Assigned to an Individual ONLY

molly.martin924

I'm currently having trouble figuring out how to capture the number of status specific projects assigned to an individual. I've attempted the "COUNTIFS" formula many times, but can't seem to configure it so that it counts the number of projects by assignee IF the project status is one of the following:

Not Started

In Progress

Sent for Corrections

The current formula is counting every single project (Completed projects included) we've ever had and our dashboard is receiving a lot of questions due to the large number of project each team member has. Please help!

Jeff M.

Sure, you'll want to repeat the same 3 status variables for each name. Try putting this one in there and see if that gives you the correct results.

=COUNTIFS({TE}, "AK", {status}, "In Progress") + COUNTIFS({TE}, "AK", {status}, "Not Started") + COUNTIFS({TE}, "AK", {status}, "Sent for Corrections") + COUNTIFS({TE}, "MM", {status}, "In Progress") + COUNTIFS({TE}, "MM", {status}, "Not Started") + COUNTIFS({TE}, "MM", {status}, "Sent for Corrections") + COUNTIFS({TE}, "CP", {status}, "In Progress") + COUNTIFS({TE}, "CP", {status}, "Not Started") + COUNTIFS({TE}, "CP", {status}, "Sent for Corrections") + COUNTIFS({TE}, "SH", {status}, "In Progress") + COUNTIFS({TE}, "SH", {status}, "Not Started") + COUNTIFS({TE}, "SH", {status}, "Sent for Corrections")

Jeff M.

Try this formula and see if this fixes your error. Otherwise you may need to account for the parent format and add the descendants function. DESCENDANTS Function | Smartsheet Learning Center

=COUNTIFS({TE}, "AK", {status}, "Not Started")

molly.martin924

https://community.smartsheet.com/discussion/comment/317217#Comment_317217

Hey Jeff,

Thanks so much for your reply! I was able to use the "COUNTIFS" formula in the way you mentioned successfully, but am needing to also account for 2 additional statuses ("Not Started" and "Sent for Corrections") to ensure my reporting data isn't counting the completed projects. Got any tips for adding multiple criteria for this formula? I tried experimenting with adding an "AND" and was unsuccessful. Thanks in advance!

Jeff M.

Sure, you'll want to repeat the same 3 status variables for each name. Try putting this one in there and see if that gives you the correct results.

=COUNTIFS({TE}, "AK", {status}, "In Progress") + COUNTIFS({TE}, "AK", {status}, "Not Started") + COUNTIFS({TE}, "AK", {status}, "Sent for Corrections") + COUNTIFS({TE}, "MM", {status}, "In Progress") + COUNTIFS({TE}, "MM", {status}, "Not Started") + COUNTIFS({TE}, "MM", {status}, "Sent for Corrections") + COUNTIFS({TE}, "CP", {status}, "In Progress") + COUNTIFS({TE}, "CP", {status}, "Not Started") + COUNTIFS({TE}, "CP", {status}, "Sent for Corrections") + COUNTIFS({TE}, "SH", {status}, "In Progress") + COUNTIFS({TE}, "SH", {status}, "Not Started") + COUNTIFS({TE}, "SH", {status}, "Sent for Corrections")

molly.martin924

This worked! Would have never thought to add the "+". Thanks so much for your help!