Calculate totals across multiple project sheets , using another sheet to get the list of projects

Hello

I have project request sheet which is used to track all projects that the LE team are asked to do. The LE team consists of 3 team members.

As projects on the request sheet are approved, team members are allocated to the project. On the project sheet the project status is set to approved.

I would like to know how to calculate the number of tasks that are not in a Completed status across approved projects that each member has been allocated , using the project request sheet to identify the projects to check.

I have done this in the past using a COUNTIFS statement and selecting each project individually.

=COUNTIFS({Assigned To P1}; Primary7; {Status}; <>"Complete") + COUNTIFS({Assigned To P3}; Primary7; {State 3}; <>"Complete") + COUNTIFS({Assigned To P4}; Primary7; {State 4}; <>"Complete") + COUNTIFS({Assigned To P5}; Primary7; {State 5}; <>"Complete") + COUNTIFS({Assigned to P6}; Primary7; {State 6}; <>"Complete") + COUNTIFS({Assigned to P7}; Primary7; {State 7}; <>"Complete") + COUNTIFS({Assigned to P8}; Primary7; {State 8}; <>"Complete") + COUNTIFS({Assigned to P9}; Primary7; {State 9}; <>"Complete") + COUNTIFS({Assigned to 10}; Primary7; {State 10}; <>"Complete") + COUNTIFS({Assigned to P12}; Primary7; {State 12}; <>"Complete") + COUNTIFS({Assigned to P13}; Primary7; {State 13}; <>"Complete")

However each time the person is added to a new project, that project needs to be added to this COUNTIFS statement. I'm looking for something more dynamic, that will retrieve the list of Approved projects from the Project Request sheet and then go through each of those projects to get the count

Find more posts tagged with

Accepted answers

All comments

Colleen Patterson

We have utilized reports to address items like this in the past, and then run the counts off the report. Since the report will auto update and collect the new information, it is a one and done formula.

The criteria you are using for example does not equal complete, can be addressed with filters.

Then you can group by team member and add summaries to get active counts for each.

aneekahTAL

Thanks for pointing me in the direction of using reports.

How do I show the summaries in a chart?

aneekahTAL

No worries - I figured it out on the dashboard using the Chart widget.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.