AVERAGEIF keeps returning "#Invalid Value"

I am creating Sheet Summary fields which count and average various values based on criteria.

In one cell, a formula that works is =COUNTIFS(Manager:Manager, HAS(@cell, "Tony"), [Complete Tasks]:[Complete Tasks], "1") / ([Last Week Helper Column]1 + 1)

For the record, Manager is a Contacts Column.

However, when I try a similar equation to calculate Average Duration based on the value of the Manager, I keep getting #Invalid Value. Both of the below equations do NOT work.

=AVERAGEIF(Manager:Manager, HAS(@cell, "Tony"), [Duration Cheat]:[Duration Cheat])

=AVERAGEIF(Manager:Manager, "Tony", [Duration Cheat]:[Duration Cheat])

I have verified that Duration Cheat is displaying a numerical value, and can add and sum when not using the AVERAGEIF function.

What am I doing wrong?


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    =AVERAGEIF(Manager:Manager, "Tony", [Duration Cheat]:[Duration Cheat])

    This one works for me. Are you 100% confident that all cells are formatted correctly, or is it possible the Sheet Summary Field you are placing the formula into is formatted as something other than Text/Number?

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Here is the test sheet I made using this formula: =AVERAGEIF(Manager:Manager, "Tony", [Duration Cheat]:[Duration Cheat])

    As you can see, I threw a few curve balls in and it still functions as expected. Here are a couple of formulas you can try for troubleshooting:

    =COUNTIFS(Manager:Manager, "Tony")

    =COUNTIFS([Duration Cheat]:[Duration Cheat], ISNUMBER(@cell)

  • Thank you, but as I mentioned in the OP, the Manager column is a Contact column. Not just text, and the COUNTIFS equation works fine. The AVERAGEIF equation, including the one you provided, is still returning Invalid Value.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    My apologies, I completely missed that this was a contact column. Here is the formula I now have working in my test sheet. I am still not sure why this isn't working for you. =AVERAGEIF(Manager:Manager, "Carson Penticuff", [Duration Cheat]:[Duration Cheat])

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!