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})
Best Answer

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"))
Answers

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

Does this work?

Thank you so much. I was able to get it to work with the collect formula.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.5K Get Help
 367 Global Discussions
 202 Industry Talk
 432 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 447 Show & Tell
 29 Member Spotlight
 1 SmartStories
 285 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!