Countifs but not include the subcategory

rgallego18 · 03/14/19

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!

Marcin P · 03/14/19

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 · 03/14/19

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 · 03/14/19

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 · 03/15/19

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 · 03/15/19

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 · 03/15/19

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.

Countifs but not include the subcategory

Comments

Categories