How to calculate total percent completion only within rows that meet a specific criteria?

Barbara Silva

I have developed a project schedule that is detailed and has information that should not be shared with the client. In order to provide the client with a summary view, I am using a checkbox column to mark the rows that should be client facing, and pulling them to be shown separately on a report. In parallel, I developed a dashboard that includes the overall project percentage completion (calculated automatically within the sheet), based on the detailed information from the comprehensive internal schedule.

My question is, what is the most efficient way to calculate the total project percentage completion only within the rows that are client facing (that have the "Client Facing" checkbox column marked)?

I would like to include that data on a separate client facing dashboard, but it would have to align with the level of information they have access to within the other sheets/reports. Thank you in advance. 

Paul Newcome

@Barbara Silva I am having one of those days apparently... The second range in the COUNTIFS was not needed. I had just wrapped up a SUMIFS that requires a second range. Ugh. It was actually a misplaced closing parenthesis. I need some coffee. Haha

=IF([Client Facing]@row = 1, IF(OR(COUNT(CHILDREN(Status@row)) = 0, COUNTIFS(CHILDREN([Client Facing]@row), 1) = 0), 1)

Genevieve P.

Hi Barbara,

In your source sheet you could set up a "Client Percent Complete" field in the Sheet Summary section of your sheet. Then you can use an AVERAGEIF formula to average together the percentages, but only in the rows where the helper checkbox is checked. For example:

=AVERAGEIF(Checkbox:Checkbox, 1, Status:Status)

You can read more about Sheet Summaries by clicking here. You would want to format the field to be a Percent type of field, like so:

Then once you have the calculation, use a Metric Widget in your dashboard to bring in this data from the sheet. Users don't need to be shared to the underlying sheet to see a Metric Widget on a dashboard. You can learn more about the Metric Widget by clicking here.

When selecting your sheet, make sure to choose the Sheet Summary Data option to find your percentage.

Let me know if this will work for you!

Cheers,

Genevieve

Barbara Silva

Thank you, @Genevieve P. One of the things I saw when I was trying to find a solution to my problem was that when Smartsheet calculates the percentage completion at the project level, it also considers the duration of a task in the calculation (i.e., a 30-day task being completed does not weight the same as a 2-day task being completed). Is there a formula that Smartsheet offers that would automatically account for that?

If not, as I read your comment above I wonder if I should add a column multiplying the duration of the task by the percentage completion to have a final percentage accounting for the number of days ("weighted % completion"), and apply your average formula to that column(...) Thoughts?

Thank you once again, I really appreciate it.

Genevieve P.

Hi Barbara,

You're correct, there is a weighted percentage when you have enabled Project Settings. The formula I mentioned above can be used to reference that weighted percent column - you wouldn't need to create a separate percent column if that's what you meant.

We don't currently have a specific function that automatically calculates this weight... in order to create a formula that would help replicate this, could you maybe post a screen capture of your sheet in grid view? (Blocking out any sensitive data). It would be useful to see your set-up and the different types of rows you're trying to show to thing if there's an alternative way to present this information and calculate a total.

Thanks!

Barbara Silva

Thank you, @Genevieve P. Last question - still within the same issue I'm trying to solve but under a different perspective:

Is there a formula that I could use to SUM numbers in a column, but NOT accounting for values that are in parent rows?

The formula would go on a sheet summary as you suggested; the reason not to account for values in parent rows, is to avoid duplication, since they are a reflection of the information in their child rows.... Thank you!

Paul Newcome

@Barbara Silva

To account for non-parent rows only, you would need to use a "helper" column. You could use a checkbox type for example and use a formula in this column to check the boxes for any rows that meet your desired criteria of not being a parent row.

=IF(COUNT(CHILDREN([Other Column]@row)) = 0, 1)

Then you could use a SUMIFS statement to look at rows where the boxes are checked.

=SUMIFS([Add Column]:[Add Column], [Helper Column]:[Helper Column], @cell = 1)

The reason you would need to go this route is because hierarchy functions cannot use @cell references. This means that without the helper column the formula would read:

=SUMIFS([Add Column]:[Add Column], [Add Column]:[Add Column], COUNT(CHILDREN(@cell)) = 0)

Using the @cell reference inside of the CHILDREN function throws an #UNPARSEABLE error.

Genevieve P.

I agree with Paul! I would also suggest that you could hide this checkbox column if you don't want it visible in your sheet.

Barbara Silva

Thank you so much, @Genevieve P and @Paul Newcome. I have added the screenshot Genevieve suggested with some explanation, in case you feel like being extra kind and sending your thoughts my way.

Do you happen to know how I can schedule a 1:1 with someone at Smartsheet to go over this? A colleague had done it in the past but I am not sure how to go about it.

I thought I had reached a solution (thanks to you guys), but now I realized that, if only a parent row is marked as client facing and none of its child rows are marked, my percentages would still be skewed since I am now disregarding values in the parent rows. :(

Thanks once again; I do appreciate it.

Paul Newcome

I am thinking maybe we need to adjust your [Child Rows] column.

We already know how we want to check the box for child rows. Now we just need to figure out how to check the box for parent rows that have no child rows that are client facing.

I assume the [Client Facing] column is manually checked? If so, it may be easier to build off of that instead.

So we start with Child rows...

If there are no child rows on @row then we want to check the box.

=IF(COUNT(CHILDREN(Status@row)) = 0, 1)

But we also want an exception that says to ignore the count of children and check the box on @row even if it is a parent row if there are no other children checked.

=IF(COUNTIFS(CHILDREN([Client Facing]@row, 1) = 0, 1)

So now we can nest these two criteria in an OR statement since they have the same output.

=IF(OR(COUNT(CHILDREN(Status@row)) = 0, COUNTIFS(CHILDREN([Client Facing]@row, 1) = 0), 1)

But we only want to run this if [Client Facing]@row is checked, so we wrap the entire formula above in another IF statement that first checks that particular criteria. If it IS checked, then it will run our above formula, but if it is not checked, then it won't run anything and will leave the box checked.

=IF([Client Facing]@row = 1, run this other formula)

=IF([Client Facing]@row = 1, IF(OR(COUNT(CHILDREN(Status@row)) = 0, COUNTIFS(CHILDREN([Client Facing]@row, 1) = 0), 1))

Try plugging that in and let us know if it works for you.

Genevieve P.

Hi Barbara,

I agree with Paul!

In regards to your question about a 1:1 with a Smartsheet employee, based on the account associated with your email address you are a licensed member of an organization with a Professional Support package. This means you are eligible to book Pro Desk sessions with the access code you use to access the Center of Excellence. (Let me know if you don't know this code and I can message you privately with it - or the System Admin for your organization should know what it is).

Here is the information about booking Pro Desk sessions: https://help.smartsheet.com/articles/2478411

Your topic would likely be Project Management, for this type of question, although it could fall under Reports & Dashboards as well or even Formulas and Automations (although I think Paul has the formula bit covered!). If you end up booking a Pro Desk session, it would be helpful for the agent to see this thread before hand, so I would suggest providing them with the link to this post.

Hope that helps!

Genevieve

Barbara Silva

Hi @Genevieve P, thank you so much.

Would you please send the code to me?

Thanks once again,

Barbara.

Genevieve P.

No problem! I've messaged you directly within the Community.

Barbara Silva

Hi @Paul Newcome, I first thank you very much for all the help.

I tried plugging in the final formula, and it came back as "unparseable." I then plugged in each of the formulas you mentioned as you walked me thru the explanation to try and see where the error could be coming from, and below is what I got:

Paul Newcome

@Barbara Silva My apologies. That was my fault. It is with the COUNTIFS.

COUNTIFS(CHILDREN([Client Facing]@row), CHILDREN([Client Facing]@row), 1) = 0

I forgot the second range int there. Try dropping that in place and let me know how it goes.

Barbara Silva

Hi @Paul Newcome, not at all; I wouldn't have even gotten this far without the help.

I did do what you suggested, but some of the cells are returning an "#incorrect argument set" message (refer to below screenshot). I did notice that only the cells in rows marked as client facing are returning that message. Thoughts?

Thanks once again.

Paul Newcome

@Barbara Silva I am having one of those days apparently... The second range in the COUNTIFS was not needed. I had just wrapped up a SUMIFS that requires a second range. Ugh. It was actually a misplaced closing parenthesis. I need some coffee. Haha

=IF([Client Facing]@row = 1, IF(OR(COUNT(CHILDREN(Status@row)) = 0, COUNTIFS(CHILDREN([Client Facing]@row), 1) = 0), 1)