Count Progress Within a Specific Parent Section

Carli Baio

Hello, I am new to Smartsheet so I may not be using the correct terminology. I am trying to count the number of tasks set to a certain "Progress" within my project sheet.

Ideally, I would like to break up this count by the different parent sections we have set up (Government Officials, Healthcare & Medical Offices, Non-Profits, etc.)

For example, I'd like to be able to show in the "Government " section I have 3 projects complete, 1 in progress 2 not started. While in the Healthcare section, I have 5 complete, 6 In progress, etc.

Currently, I am using the following formula to count all the Progress results based on their status (=COUNTIF({Raw Data Sheet Range 1}, ="Not Started")

Any ideas on how I can break this up by section? I attached a reference to the sheet. Thanks!

Mike Wilday

The easiest way to do this is to add a helper column called Parent:

In the helper column add this formula in the first row...

=IF(ISBLANK(PARENT()), [Organization Name]@row, PARENT())

Then right click on it and make it a column formula.

This will add the parent name to your entire sheet. You can hide the column once you have it set up and working.

Then you can do COUNTIFS formula for each organization name to get those counts.

=Countifs({Parent Range}, "Government Offices", {Progress Range}, "Not Started")

=Countifs({Parent Range}, "Government Offices", {Progress Range}, "Complete")

You'll need to replace those ranges I created with Cross sheet references of those columns from your own sheet. They may have different names as you create them, but if you target those columns in your original sheet they will work for you.

Mike Wilday

The easiest way to do this is to add a helper column called Parent:

In the helper column add this formula in the first row...

=IF(ISBLANK(PARENT()), [Organization Name]@row, PARENT())

Then right click on it and make it a column formula.

This will add the parent name to your entire sheet. You can hide the column once you have it set up and working.

Then you can do COUNTIFS formula for each organization name to get those counts.

=Countifs({Parent Range}, "Government Offices", {Progress Range}, "Not Started")

=Countifs({Parent Range}, "Government Offices", {Progress Range}, "Complete")

You'll need to replace those ranges I created with Cross sheet references of those columns from your own sheet. They may have different names as you create them, but if you target those columns in your original sheet they will work for you.

Mike Wilday

In the helper column add that first formula in the first row. Then right click on it and make it a column formula. This will add the parent data to you sheet and allow you to do a COUNTIFS based on the parent name.

Mike Wilday

Here's an example of the formulas at work in the Summary Section of my test sheet.

One question I have is: do you have an In Progress or Active range that you need to count as well? If you want to count anything other than "Complete" you can also do <>"Complete" instead of "Not Started" to count any other status... just a thought.

Carli Baio

Thanks for the quick reply! I have a few statuses but am only looking to count "Not Started," "In Progress," and "Completed"

The parent column worked great, however, I am still having some trouble with the COUNTIFS formula, when I add it to my sheet I keep getting an "Incorrect Argument" error. Any Ideas?

Mike Wilday

Can you share your formula as you've entered it in Smartsheet? A copy and paste of the exact formula will help me identify the issue.

Carli Baio

No problem! Looks like I was missing the "s" in COUNTIFS. We're all set now. Thanks so much for the help!

Mike Wilday

Great! Awesome. Glad I could help you!

Carli Baio

One more question for you! I tried using that same formula to track the "Completed" "Calls to Action". For our call to action, we have multi-select options. When both are selected and progress is "complete" it only counts towards one of the categories:

I used the following formula =COUNTIFS({Raw Data:Robinson Range 3}, "Love Drop", {Raw Data:Robinson Range 2}, "Completed")

But it is only counting towards the "Love Drop," and not the "Free Entree Card Drop" any idea?

Thanks!

Mike Wilday

So in a multi-select column you would have to adjust the formula a bit.

=COUNTIFS(Status:Status, "complete", [Call to Action]:[Call to Action], HAS(@cell, "Love Drop"), Parent:Parent, "Government Officials")

This formula would count every love drop in your Government officials parent that is complete. Adjust "Love Drop" to your other call to action to count for it.

Carli Baio

For this formula, we'd be trying to get the total for the entire sheet. I tried dropping the end portion, however I an error message

Mike Wilday

=COUNTIFS(Status:Status, "complete", [Call to Action]:[Call to Action], HAS(@cell, "Love Drop"))

Is this the formula you were using?

Carli Baio

Yes, and it was showing as "#UNPARSEABLE" I'm trying to count each Call to Action in their own segment (ie Love Drops, Menu Drops, etc) for the entire sheet, It doesn't need to be broken up by the parent.

Thanks for the help!

-Carli

Mike Wilday

So its working now?

Carli Baio

No, sorry it is not working.

Mike Wilday

Can you share a screenshot? I am seeing that this formula does work. You'll see two Free Entree's and three Loves that are complete and in the column.