How to create an average using SUMIF as the denominator

USERPL
USERPL ✭
edited 10/16/20 in Formulas and Functions
Screenshot 2020-10-16 142643.png

I am trying to create an array of construction costs then extract the square foot costs. The issue I have is not all projects have the scope of work. For instance, I have new lighting on some but not all projects. So I want to create an average using the the total costs divided by just the cells that have square footage associated with the cost.

=((SUM(P11:BY11)/(SUMIF($P$2:$BY$2,P11:BY11>0)))) Where row 11 is the dollar value (when used) and row 2 is the square footage of the project

· Share on FacebookShare on Twitter

Answers

  • USERPL
    USERPL ✭
    edited 10/16/20


    · Share on FacebookShare on Twitter
  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @USERPL

    Can you please include the column headers so I can see what your formula is referring to?

    Second question is are you receiving an error or is something not working with your formula?

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!