Countifs but not include the subcategory

edited 12/09/19 in Smartsheet Basics

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


    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
    L_123 ✭✭✭✭✭✭
    edited 03/14/19

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


    Or approaching it from the children angle


    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


    Which you can use as your criteria for your formula.

  • Paul Newcome
    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
    Marcin P
    edited 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
    Paul Newcome ✭✭✭✭✭✭



    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
    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.