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??
Thanks!
Best Answers

Here you go, try this:
=COUNTIFS(CHILDREN(Progress@row), OR(@cell = "Quarter", @cell = "Half", @cell = "Three Quarter"))
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

@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"))
Regards,
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!
Answers

Here you go, try this:
=COUNTIFS(CHILDREN(Progress@row), OR(@cell = "Quarter", @cell = "Half", @cell = "Three Quarter"))
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

@Jason Tarpinian Ah thank you, that worked!!

@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"))
Regards,
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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!