Countifs but not include the subcategory

rgallego18
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!

2019-03-14_1619.png

Comments

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

    =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
    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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    ss_screen1.jpg

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com