Help with "AND" formula

I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an "Invalid Data type". I've confirmed all references are correct. Anyone have ideas?

=AVERAGEIF(AND({Complexity Year}, "2019", {Product}, "ESP"), {Complexity Score New})

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

AVERAGEIF and AND do not play well together. Try this one, it collects all values that meet your criteria and then averages them. I am assuming {Complexity Score New} is the range with the values you would like to average.

=AVG(COLLECT({Complexity Score New}, {Complexity Year}, "2019", {Product}, "ESP"))

All comments

Carson Penticuff

=AVG(COLLECT({Complexity Score New}, {Complexity Year}, "2019", {Product}, "ESP"))

Pat Canning

Does this work?

Katie S.

Thank you so much. I was able to get it to work with the collect formula.

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.