Count Parent rows only based on checkboxes.

Sherry Fox

Hello again! I am trying to create a better sheet. I have what I would call a "template". For each of the check boxes in the Report column, these correspond to a parent row. I would like to be able to have a formula that would count ONLY the parent rows. So, in my screenshot below as an example, the 3 checkboxes I have circled would be numbered as 1, 2, and 3. All other rows (child rows), would remain blank. Is this possible?

Hollie Green

I'm not sure I fully understand your question but you can create a helper column to use in a formula to count Main Parent rows

Create a helper column I usually call it level use the formula

=COUNT(ANCESTORS())+1)

The above will give all of your main parent rows a value of 1.

You could then use a Countifs to pull in the count of the parent rows that are also checked

=Countifs([Level:Level],1,[Report:Report],1)

Sherry Fox

@Hollie Green ,

The first formula gives me #UNPARSABLE. Is it supposed to reference any cell? I have it in a column named "Level" like you suggested, but it is just not working. As I mentioned I only want the parents to be counted. I plan to use this count in order to do an Index/Match from another sheet. That other sheet has just a list of the Release #s, which are on the Parent rows here. The idea is is makes this list easier to manage. Currently I have to do everything, but with this process, it would be easy for me to allow someone else to manage.

Hollie Green

The Count(ANCESTORS()) +1) Is referencing it's own column and should work since you have parent rows can you paste the formula maybe there is a typo somewhere in it

Sherry Fox

@Hollie Green ,

No, the formula continues to result in #UNPARSABLE. I copied the formula directly from your post, so I don't anticipate a typo. The formula I am using is:

=Count(ANCESTORS()) +1)

I just noticed a typo, and tried

=COUNT(ANCESTORS() + 1)

I tried this, and dragged the formula down (even tried pasting), and all rows showed a value of 1. At no time did my ID formula work to count only the parent level records.

Hollie Green

apologies there was an extra ). It should be =COUNT(ANCESTORS()) + 1 This will label all of your parent rows with a 1. I also think I partially miss read your question. The above for the Count(Ancestors will still be needed but you will also need to do the below

add an # Auto number type column as well- I typically label it Row ID you can name it whatever you like would just need to change the name in the formula if you do and set up a formula in your ID column

=IF(Level@row = 1, SUMIFS(Level:Level, Level:Level, 1, [Row ID]:[Row ID], <=[Row ID]@row), "")

I copied and pasted these formulas directly from my smartsheet shouldn't be any typos or extra ) this time.

Sherry Fox

@Hollie Green

The first formula works in the Level column. 1 for a parent, and 2 for a child. But I used your new second formula, and it still shows as #UNPARSABLE. I did change the format to auto number like you stated. Plus I did a save and refresh of the sheet, just in case. Here is where I am. And I copied your formula for the ID, exactly as you listed. So I am confused. And thanks for all your help, I do appreciate it. This will save a lot of time once we figure it out! LOL

Sherry Fox

@Hollie Green ,

Somehow that last post did not go through when I wrote itg. I did however find a solution on another post.

Hollie Green

Glad you found a solution the reason the formula gave you an unparseable is because you named your row Auto so it was not referencing the correct column. I am posting in case someone else comes across this post looking for a similar solution.

Sherry Fox

@Hollie Green ,

Actually when I found this solution, I deleted your column named Level. I appreciate all your time and help!