Sum Children that Meet Criteria out of Total

Smartsheet Community:
I am Trying to Sum the Children with Criteria in a Different Column. For Example:
Column A: Total =100
Column B: In the Sprint
Column A Formula: 50 out 100 in Sprint
Not sure if I start with Sum the Children or If Count Children First. Tried both but unsuccessful.
Thanks
Best Answer
-
Smartsheet Community:
Thanks for All the Responses! I figured it Out. Take a Look:
Formula 1: Column 1
="In: " + COUNTIFS(CHILDREN([In Sprint?]@row), "In") + " Of " + (COUNT(CHILDREN()) + " Tickets")
Formula 2: Column 2
="In: " + SUMIFS(CHILDREN(), CHILDREN([In Sprint?]@row), "In") + " Of " + (SUM(CHILDREN()) + " SP")
Results:
Column 1: In: 6 Out of 24 Tickets
Column 2: In: 63 Of 251 SP
Answers
-
Hi @Ray B
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
βοΈ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Ray B
You'll want to use a COUNTIF to see if the Children rows in Column B say "In the Sprint".
Try this:
=COUNTIF(CHILDREN([Column B]@row), "In the Sprint")
You need to specify within the CHILDREN function that you're looking at Column B for this criteria. Does that make sense?
Let me know if this is what you're looking to do. If not, I agree with @Bassam Khalil that it would be helpful to see a screen capture!
Cheers,
Genevieve
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
Smartsheet Community:
Thanks for All the Responses! I figured it Out. Take a Look:
Formula 1: Column 1
="In: " + COUNTIFS(CHILDREN([In Sprint?]@row), "In") + " Of " + (COUNT(CHILDREN()) + " Tickets")
Formula 2: Column 2
="In: " + SUMIFS(CHILDREN(), CHILDREN([In Sprint?]@row), "In") + " Of " + (SUM(CHILDREN()) + " SP")
Results:
Column 1: In: 6 Out of 24 Tickets
Column 2: In: 63 Of 251 SP
-
I'm glad you figured it out! Well done. π
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!