Trying to set up my Sheet Summary formulas so I can create a dashboard

Options

I've been trying to set up my Sheet Summary formulas to look through one column, pick out the Not Completed projects and then take an average of the days outstanding.

Not Complete Fx: =COUNTIFS(Contractor:Contractor, "BENTON Paving", Status:Status, "Not Complete")

Unparseable Fx: =COUNTIFS(Contractor:Contractor, "BENTON Paving", Status:Status, "Not Complete"), AVERAGEIF([Days Outstanding]:[Days Outstanding])



Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    AVERAGEIF only allows for 1 criteria which you haven't defined in the formula above plus you cannot string two formulas together in such a way as you're trying to do. You're going to need to set up a helper column that is something like a checkbox column with a formula. It'll check the box if it's both Benton Paving and Not Completed. Then have the summary column AVERAGEIF the helper column is checked. Do you know how to set that up or do you need help?

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    AVERAGEIF only allows for 1 criteria which you haven't defined in the formula above plus you cannot string two formulas together in such a way as you're trying to do. You're going to need to set up a helper column that is something like a checkbox column with a formula. It'll check the box if it's both Benton Paving and Not Completed. Then have the summary column AVERAGEIF the helper column is checked. Do you know how to set that up or do you need help?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!