COUNTIFS with CHILDREN and Status Bar

I am trying to count the number of child rows that have a status bar symbol of Quarter, Half, or Three Quarters. Example: if 2 child rows are half, one is full, one is empty, and one is three quarters, the count should be 3 to account for the two halfs and one three quarters, and negate the empty and full ones. Just trying to count who is in progress).

I'm really struggling with the formula. This is what I have: =COUNTIFS(CHILDREN([Progress]@row), "Quarter", CHILDREN([Progress]@row), "Half", CHILDREN([Progress]@row), "Three Quarter")))

I always struggle with COUNTIFS when CHILDREN are involved. What am I missing here??


Best Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓

    Here you go, try this:

    =COUNTIFS(CHILDREN(Progress@row), OR(@cell = "Quarter", @cell = "Half", @cell = "Three Quarter"))

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Kristina S Otten In the COUNTIFS and SUMIFS formulas, there's an implied "AND" in them, because all the criteria must be true in order for a row to be counted or a row's value summed. The way around that is to use OR when you want to include all cells in a range that are equal to this value or that value or this other value.

    Try this (disclaimer - I've never done this with status bar symbols, so the below is untested.)

    =COUNTIFS(CHILDREN([Progress]@row), OR(@cell = "Quarter", @cell = "Half", @cell = "Three Quarter"))


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!