# Formula for counting total of a specific word in a parent column only (not including child)

✭✭✭
edited 12/08/23

Hi everyone,

Is there a formula to count the total rows of a parent row (instead of making a checkbox section for parent row)?

Need help with this the most:

Is there a formula I can use in summary sheet that can help me find the total count in a different sheet for a specific word (Ex: "NC", "FCAR", "total of all the words", etc) in a column (Finding Type) of a parent row (made a separate column for parent row with checkbox)?

Tags:

• ✭✭✭✭✭✭

Are you able to provide some screenshots for context?

• ✭✭✭

Thank you for helping me!

Here is an example of a Summary Sheet I am trying to use to find the total for each category.

Here is an example of the Updated Metrics Sheet (can't show the real example), but I would like to find a formula that can count the parent rows only with a specific finding type (example: "NC") and specific BCAB Status (example: "STGV")

Thank you!

• ✭✭✭✭✭✭

You are going to need a helper column on the source sheet with a formula to indicate which rows are parent rows.

=IF(COUNT(CHILDREN([Finding Type]@row))> 0, 1)

• ✭✭✭

Thank you for your help! Once I input it for the parent row. (For example "NC", "FCAR", etc). I tried =COUNTIFS(Finding Type), FIND("NC", @cell) > 0), but it gave a count that included Parent and all Childs below it. How exactly do I find the specific parent row with that finding type word used?

• ✭✭✭✭✭✭

You would have to include another range criteria set within the COUNTIFS to filter based on the helper column being checked.

• ✭✭✭

That is what I'm trying to figure out and I am still struggling. Does anyone know what formula I should use?

• ✭✭✭✭✭✭

You would use a COUNTIFS formula.

You currently have

=COUNTIFS(range, criteria)

You simply need

=COUNTIFS(range, criteria, range, criteria)

with the second range/criteria set evaluating the helper column.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!