Do not want Cells with "N/A" figured in my formula

Jason Cocco

=SUM(CHILDREN()) / (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ""))

I am using the above formula for percent complete in my project plan as seen by my picture. My question is when certain parts of the project have not been started yet how do you exclude the cell with the "N/A" value in it from being calculated?

Genevieve P.

Hi Jason,

What if you adjusted your formula to as follows:

=SUM(CHILDREN()) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), ="N/A"))

 

This would add the values of your child rows together, then it would divide that number only by the cells with an actual percentage value. This would also skip over any blank cells though, and not include those in the division calculation.

Were you looking to add in blank cells as if they were 0 percentage? If so, you could take away the N/A cells like so:

 

=SUM(CHILDREN()) / ((COUNT(CHILDREN()) - COUNTIF(CHILDREN(), ="N/A")) + COUNTIF(CHILDREN(), ""))

See my example image of both formulas, below. 



Cheers!

Genevieve