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

APWURK

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! 

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 

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.