Trying to use Averageif for 2 criteria with Collect

roxBVL

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.

Carson Penticuff

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.

roxBVL

https://community.smartsheet.com/discussion/comment/392157#Comment_392157

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