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

Tina1234

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.

Thank you in advance.

Nick Korna

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

Colleen Patterson

@Tina1234

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])

Tina1234

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

SoS | Dan Palenchar

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).

https://app.smartsheet.com/b/publish?EQBCT=d8378e19d61d4941888b310abc4bd418

Nick Korna

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).

Tina1234

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

@Nick Korna I want the lowest level of children

Nick Korna

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