Tips for Showing Count of Answers Completed on a Smartsheet?

Steven Nolan · 07/11/18

If I have a section with three rows underneath my heading, I'm able to easily use a formula to show the count of tasks completed/responded to out of my total number:

="Introduction: " + COUNT(Response4:Response6) + " of " + COUNT(CHILDREN())

However, if I have a section with multiple sub-sections, I'm struggling to use a formula to show this information. For example, my "Logistics" section has 4 sub-sections, and the formula does not work for this. It will allow me to add more than my total number of children. See screenshot 2.

Does anyone have ideas on how to use a formula in this situation?

2018-07-11 13_40_59-Client Discovery Questionnaire - Smartsheet.com_.png

2018-07-11 13_43_59-Client Discovery Questionnaire - Smartsheet.com_.png

Luke X · 07/12/18

Hi Steven,

In short, it depends on how many sheets you need to implement this on.

If I'm understanding correctly, you'd need to have each subsection have a count(children) then sum(children) for the parent to get the answers.

Alternatively, a more complex formula leverages an if condition to say something along the lines of:

if I am a child, throw 1 (or 0 if you wanna count unanswered)
if I am a parent, sum the number of children (or answered children, etc)

This is done by creating a function that leverages ancestors and essentially asks "am I the lowest level".

Best,

Luke

Tips for Showing Count of Answers Completed on a Smartsheet?

Comments

Help Article Resources

Categories