COUNTIFS with CHILDREN and Status Bar

Kristina S Otten

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

Thanks!

Jason Tarpinian

Here you go, try this:

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

Jeff Reisman

@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"))

Jason Tarpinian

Here you go, try this:

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

Kristina S Otten

@Jason Tarpinian Ah thank you, that worked!!

Jeff Reisman

@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"))