Trying to use Averageif for 2 criteria with Collect
Hello all!
I am trying to find the average of a helper column, but it has to match a (1) certain date and (2) priority. here is what I am using, and it returns #Incorrect Argument.
=AVERAGEIF(COLLECT({UpKeep Q3 - All Overdue}, {UpKeep Q3 - Due}, $Date@row, {UpKeep Q3 - Priority}, "HIGH"))
Notes: UpKeep Q3 is the raw data sheet/helper column. $Date@row is the date I want to match the due date to (yes both are in date format!)
"All Overdue" is the helper column I would like to average (it's the addition of 2 other helper columns). Priority matches case of the raw data, I have tried both typing it in and linking a cell within the sheet with the same word. Any help would be appreciated! Can include screenshots if needed.
Answers
-
The AVERAGEIF is the issue. You are filtering the results down within your COLLECT() statement to only those cells that match your criteria. Try replacing AVERAGEIF with AVG.
-
I had to do a brief facepalm. Thank you. It is now a #Divide by zero error. I think I am going to make some helper columns
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 395 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!