# Formula to Average a Children

Options

Hello, I have a column of costs that i want to average in a parent row, however I only want to average values that are greater than \$0.00 and have an active status (seperate column). Below is an example.

Status Cost

Parent Row Active X.XX

Child Row1 Active \$4.00

Child Row2 Inactive \$4.00

Child Row3 Active \$2.00

Child Row4 Active \$0.00

The formula would be in the Cost cell of the parent row and it should read \$3.00 in this scenario since it would be averaging only Child Row1 (active and greater than \$0) and Child Row3 (active and greater than \$0)

I was trying to average them by summing the count of all children rows that are active and dividing that by child rows that are active and above 0, but i cant seem to straighten out the formula.

Any help appreciated.

• ✭✭✭✭✭✭
edited 01/27/20
Options

=sumif(children(Status@row), "Active",children())/countif(Children(),not(@cell = 0))

Give that a try.

• Options

Thats close to what I had. The issue is that it when it executes the countif function it counts all rows that are not 0, but i need it to count all rows that are not 0 and are active.

• ✭✭✭✭✭✭
Options

=sumif(children(Status@row), "Active",children())/countifs(Children(),not(@cell = 0),children(status@row),"Active"

My bad. Give this one a try.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!