Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Average of 1 column based on 2 other columns

I am looking to find the average of my KPI Calculations column based on the Go-Live Quarter and the Team.

I can get it to work for 1 column but not 2

=AVERAGEIF([Go-Live Quarter]:[Go-Live Quarter], "Q1 2025", [KPI Calc]:[KPI Calc])

Tags:

Answers

  • Community Champion

    Hi @A Pisano

    Average If formula can have only one criterion. Since you have more than one, you can use this formula instead

    =AVG(COLLECT([KPI Calc]:[KPI Calc], [Go-Live Quarter]:[Go-Live Quarter], "Q1 2025", [Team column reference]:[Team column reference], "Team criterion")

    Thanks,

    Aravind GP| Delivery Manager

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • ✭✭✭✭✭✭

    Hello @A Pisano ,

    Based on the COLLECT function, can you please try the below?

    =AVG(COLLECT([KPI Calc]:[KPI Calc],[Go-Live Quarter]:[Go-Live Quarter], "Q1 2025",Team:Team,"ENTER TEAM NAME")

    Erin Horiuchi Green, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

  • Thank you both! I was able to get it to work!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions