How to create an average using SUMIF as the denominator

edited 10/16/20 in Formulas and Functions

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


  • Ramzi K
    Ramzi K ✭✭✭✭✭


    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 (

    Feel free to email me:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!