Formula to calculate task completion by owner by referencing parent rows

Hello all,

I am looking for some help with a formula. I'm super close to getting it but can't figure out the last piece.

Goal:
I have a sheet tracking task completion and want to calculate, month by month, the percentage of tasks completed for each person.

Setup:

Level 1 rows = person’s name (owner of tasks)
Level 2 rows = individual tasks
Columns include:
- MONTH (due date month)
- PROJECT CODE (person assigned)
- DESCRIPTION (parent row with owner’s name)

Current Formula:

=COUNTIFS(MONTH:MONTH, "1", [PROJECT CODE]:[PROJECT CODE], "ANDREA M")

This works, but I have to manually change the name for each person, which isn’t scalable for 100+ people.

What I Need:
A formula that:

Checks if the task was due in a specific month
Confirms it’s marked complete
Matches the name in the DESCRIPTION parent row to the name in the PROJECT CODE column in the child rows

I’ve tried a few approaches but can’t get it to reference the parent row dynamically. Any suggestions to avoid duplicating formulas for every person?

Thanks!
Andrea

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots with sample data?

Andrea Mayer

Hi Paul - sure thing. See below.

In this question, I'm trying to get the formula to see if the name in the DESCRIPTION field in level 1 is the same as the name in the PROJECT CODE field in level 2. PROJECT CODE is also a formula pulling in that name, so it will always be identical.

Paul Newcome

If you already know that the [PROJECT CODE] is the same as the parent row in the [DESCRIPTION] column because of a formula, why do you need to check it?

Sorry. I must be missing something. Where exactly are you putting your COUNTIFS?

Andrea Mayer

Sorry for the confusion.

I entering this formula into the JAN TOTAL column. So I'm asking the formula to count items that are due in January, marked complete and have the same PROJECT CODE as the parent row. So when it looks at the next persons tasks, it will only count rows that meet those parameters and their name in their parent row. Let me know if there is any easier way to do it.

What I'm really trying to avoid is having to calculate that JAN TOTAL by changing the name of the person every time I copy the formula.

Paul Newcome

You can replace the hard-coded name with a reference to the DESCRIPTION column.

=COUNTIFS(MONTH:MONTH, "1", [PROJECT CODE]:[PROJECT CODE], DESCRIPTION@row)

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.