AVERAGEIFS or AVG(COLLECT)

Mike Sikes · 03/17/22

Fairly new to Smartsheet but well versed in Excel. There has been a learning curve, but I have overcome it until now.

I need to write a formula that will average the "Days Off" Column if the "Actual Delivery Date" Column is within a certain date range.

For this example, we can use a date range in the "Actual Delivery Date" column of 1/3/22 - 1/7/22.

Any help the community can provide would be Greatly Appreciated!

James Keuning · 03/17/22

Create a helper column called helper:

=AND([Actually Delivery Date]@row > DATE(2022, 1, 3), [Actually Delivery Date]@row < DATE(2022, 1, 7))

Then use:

=AVG(COLLECT([Days Off]:[Days Off], Helper:Helper, true))

Mike Sikes · 03/21/22

This actually returned an @row error. I tried identifying row types to make sure that everything matched and everything looks good.

Answers