Sheet Summary Formula - sum all children with status of "Not Applicable"

Options
✭✭

I want to create a sheet summary formula that will sum all children in the Status column with the status of "Not Applicable". This will be used in my Smartsheet Dashboard.

• ✭✭✭✭✭✭
Options

Are you looking for a count of the rows where the status is not applicable - i.e. There are 24 rows where that status is N.A. Or, to sum data from a different column?

If the first if would be =COUNTIF(Status:Status, "Not Applicable") in the sheet summary.

If the second, If would be =SUMIF(Status:Status, "Not Applicable", [Desired Sum Column]:[Desired Sum Column])

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭
Options

I need to count the NA in the status column, but I do not want any parent rows to be counted.

• ✭✭✭✭✭✭
Options

Hello @Tina1234,

To count only the child rows I'd suggest starting by adding a column in your Sheet called Children and input the formula `=COUNT(CHILDREN())`

You can use this in a COUNTIFS() function with the general format of:

=COUNTIFS(Children:Children, 0, Status:Status, "Status option you are searching for goes here")

The published Sheet below shows this in action, you can interact with it (full screen link here).

School of Sheets (Smartsheet Partner)

If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

• ✭✭✭✭✭✭
Options

If you only have child rows (i.e. one level of indentation) this should work:

=COUNTIFS(CHILDREN(Status:Status), "NA")

If you have multiple, then it becomes a question of how far up down the chain you want to be included (levels of indentation).

• ✭✭
Options

@Dan Palenchar I typed =COUNTIFS(Children:Children, 0, Status:Status, "Not Applicable") and it #UNPARESABLE

@Nick Korna I want the lowest level of children

• ✭✭✭✭✭✭
Options

In that case, I would a helper column and formula along the lines of the one suggested by @Dan Palenchar above:

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!