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?

Find more posts tagged with

Accepted answers

All comments

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?

Tucci

@Carson Penticuff Positive.

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)

Tucci

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

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])

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.