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.
☑️ 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
-
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. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!