Asking for AVG if specific date criteria is meet.

Tbech

Asking to average the number of days worked on a project if the due date was after 2020, 12, 31.

Something in this is not working correctly.

=AVG(COLLECT({Days Total}:{Days Total}), IF({Completion Date}, >DATE(2020, 12, 31)))

Kelly Moore

Hi @Tbech

From your use of curly brackets {} I see you have a cross sheet reference. When using cross sheet references, we don't use the colons like we would if we were referencing columns on the same sheet.

Since you only have a single criteria to evaluate, you have two options of formulas. I'll show you the AVG(COLLECT) first since you started with that (always my preference as well)

=AVG(COLLECT({Days Total}, {Completion Date}, >DATE(2020, 12, 31), {Completion Date}, ISDATE(@cell)))

I added the ISDATE criteria to weed out any date errors that sometimes occur.

Smartsheet does have an AVERAGEIF formula that can be used when you have a single criteria, like you do above.

AVERAGEIF has the syntax of criteria range, criteria, [Average range (if different than the criteria range)]

Applying this to your example:

=AVERAGEIF({Completion Date}, >DATE(2020, 12, 31), {Days Total})

cheers,

Kelly

Sameer Karkhanis

You are missing the Completion Date range and your syntax is not correct. Should be,

= AVG(COLLECT([Days Total]:[Days Total], [Completion Date]:[Completion Date], DATE(2020,12,31)))

If your data is in a different sheet then,

= AVG(COLLECT({Days Total Range}, {Completion Date Range}, DATE(2020,12,31)))