Welcome!

Countifs but not include the subcategory

Hello Smartsheet Wizards,

I need your help again!

Is there a way to count the number column's data without including the subcategory?

As per screenshot, I only need to get the total number of Projects assigned to a specific person without counting the subcategory.

Is this possible?

Appreciate the help!

Find more posts tagged with

Comments

Marcin P

Hi,

I think you can add formula =count(parent(Project Name@row)) ant then count all zero values.

You'll get value 0 for each main project, so I think you'll be able to do what you need then.

L_123

I wasn't able to get that to work Marcin. I think what you were saying is something like

=countif(A:A,isblank(Parent(@cell)))

Or approaching it from the children angle

=countif(A:A,Count(Children(@cell))>0)

Neither of which seem to work

The best strategy that I've come up with is a helper column with a simple if statement dragged down through the entire column

=if(count(Children(A@row))>0,1,0)

Which you can use as your criteria for your formula.

Paul Newcome

You'll need to use the ANCESTORS function instead of the PARENT function, and a COUNTIFS instead of a COUNT.

Something along the lines of...

COUNTIFS([Project Lead]:[Project Lead], "John Smith", [Project Name]:[Project Name], COUNT(ANCESTORS(@cell)) = 0)

Marcin P

It works perfect for me. I have this formula in helper column (obviously): =if(Project Name="","",COUNT(PARENT(Project name@row))) and then just summary formula like this one: =COUNTIF(helper column:helper column, 0). Then I get result: 3. Please have a look at my screenshot.

Paul Newcome

Marcin,

This requires the use of an additional helper column. One for the parent count, and then another for the formula itself. Otherwise you will need to specify rows for the range or you will get a circular reference error.

Counting the Ancestors means you do not need a helper column. In turn you can then use an entire column for the range which provides the flexibility of additions, deletions, and sorting NOT affecting the accurate count.

Paul Newcome

The issue with counting children is that it will not count a main task that does not have any sub-tasks.

-Task 1

Sub 1.1

Sub 1.2

Task 2

Task 3

-Task 4

Sub 4.1

Sub 4.2

Basing it off of the CHILDREN count, you would only get a return of 2 when in reality there are actually 4.

Sign in to join the conversation!

Quick Links

Trending discussions

Auto-populate an email into a form to reduce error
Is it possible to auto-populate an email domain in a field where people are asked to enter their email address? For example, I would like users to only have to type their name, without needing to enter anything after the “@” symbol, to reduce the chance of personal email addresses being entered.
Suddenly View Only on all Workspaces and Sheets I own
This discussion was created from comments split from: I am an editor on a smartsheet but can't edit the sheet.
Sharing with Editors outside our company
Good afternoon! My company works closely with another ambulance service, Company B. Both companies have a Smartsheet Enterprise plan. I have created a workspace that I need members of Company B to edit. Do I need to make them members on my account if they are already members on their account? Or do I just need to add their…