Trouble with AVERAGEIF

Good day! Trying to show the completion average for an individual and thought this would work but I must be using it wrong. My reference column calculates the completion average per project, which have has some blanks, and I want the overall average of those per person. Here is what I tried, and failed. Am I on the right track?

=AVERAGEIF(COLLECT({CP Complete}, {CP Complete}, ISNUMBER(@cell)), {IM Lead}, "Carrie Lanz")

I want to return a percentage for the overall average completeion for that Lead. Thanks!



  • Hi Tina,

    Looks like there might be conflicts between the COLLECT function and the AVERAGEIF criteria, etc., though I'm not entirely certain.

    Rather than trying to debug your formula, I recommend doing it this way. Hopefully I correctly understand what is in your other sheet ranges :-).

    =SUMIFS({CP Complete}, {CP Complete}, ISNUMBER(@cell), {IM Lead}, "Carrie Lanz") / COUNTIFS({CP Complete}, {CP Complete}, ISNUMBER(@cell), {IM Lead}, "Carrie Lanz")

  • Tina Rustvold
    Tina Rustvold ✭✭✭✭✭

    I thought about COUNTIF; I just wasn't sure if I would get the overall averages for her projects. CP Complete is our column calculating Critical Path % of Completion. That column of data are percentages. A count would return a number, but would an accurate average of those percentages? Would i need another set of Metric calculations to get at those averages? Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!