Summary Formula

Hello,

I am using Smartsheet for project planning.

I would like to track adherence to plan, specifically counting the average of the Start to Plan and Finish to Plan and represent the number separately as a percentage so I can see what percentage we start and finish to plan when it happens.

I have created an Ancestors column to help identify levels of work in the sheet with the formula: =INDEX(ANCESTORS([Task Name]@row), 2).

However I am having trouble with the formula in the overall sheet summary. Can anyone help?

The output should look like this:

Overall Start to plan: **%

Overall Finish to plan: **%

Thanks,

Luke

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Cell linking

Accepted answers

All comments

Genevieve P.

Hi @lukasrobbo97

Would you be able to post a screen capture of your sheet set-up with column names showing, identifying what columns you're looking at for these percents?

It sounds like you want to see how many of the Rows have passed the Start Date based on Today's Date, and how many of the rows we have now passed based on Today's Date, compared to the total number of rows there are in the sheet. Is that correct?

If this is what you're looking to do, you can use COUNTIFS to count how many rows have a Start Date that is either In the Past or TODAY

=COUNTIFS(Start:Start, >=TODAY())

If you want to filter that down to only the child rows, you can use the helper column as another criteria:

=COUNTIFS(Start:Start, >=TODAY(), [Parent Column]:[Parent Column], "Child Row")

You'd want to replace [Parent Column] with the name of your column, and "Child Row" with whatever it is that you're using to indicate an indented row.

Then once you have this count, you can take a Count of all the rows, regardless of date:

=COUNTIFS([Parent Column]:[Parent Column], "Child Row")

With these two calculations you can find the %:

Number of rows that started before Today / Number of total task rows

=COUNTIFS(Start:Start, >=TODAY(), [Parent Column]:[Parent Column], "Child Row") / COUNTIFS([Parent Column]:[Parent Column], "Child Row")

You can do the same thing with the Finish Date column, looking to see if the date is in the Past instead of in the future:

=COUNTIFS(Finish:Finish, <=TODAY(), [Parent Column]:[Parent Column], "Child Row") / COUNTIFS([Parent Column]:[Parent Column], "Child Row")

See: TODAY Function and COUNTIFS Function

Cheers,

Genevieve

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.