Formula to identify number of parent rows with a specific status (excludes child rows)

Hello, I'm quite new to smart sheets and am having trouble parsing out this particular formula I need to make for a work metrics sheet.

Essentially our spreadsheet is comprised of many parent rows with associated childrens rows. The parent row has a column called "Status" which details where the account associated with the parent role is in our process.

I would like a formula that helps count the amount of unique statuses in the parent rows.

And just as an example, one of the unique statuses is named "Waiting For Docs"

Any direction people could point me in would be immensely helpful and appreciate!

Find more posts tagged with

Formulas

Services

Comments

Andrée Starå

Hi,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

APWURK

Hi Andree,

Thanks so much for your quick response! I'm unfortunately unable to remove all the identifying information in the document and share it with you so I screen shotted a sample of what I'm working with.

Essentially I need to create a formula that counts the status of each parent row, and ignores the children. So the formula would ideally sum up, for example, how many of the parent rows in the document say "Waiting For Docs" or any other status in our dropdown menu.

Thank you again for your help! And please let me know if there is more information you need.

-AP

SmartSheets Community Help Example.png

sean59916

AP,

You can add a reference column to identify parent from children rows and then count based on that column and status. For example (see images):

Add a reference column with the formula =COUNT(ANCESTORS([Column1]@row))
I added a new sheet, (but you can use the same) and then summarized the parents using the formula =COUNTIFS({Test Sheet 1 Range 1}, [Primary Column]1, {Test Sheet 1 Range 2}, 0)

Note, that the formula for the summary is dependent on how many parents you have above the client name.

I hope this helps?

Sean

Andrée Starå

Happy to help!

I saw that Sean answered already!

Let me know if I can help with anything else!

Best,

Andrée

Paul Newcome

To expand on Sean's solution...

If you have multiple levels of hierarchy within the sheet, this can be accounted for using his solution but changing up the criteria slightly. You would just need to know which level(s) you are wanting to count.

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…