# Asking for AVG if specific date criteria is meet.

Options
✭✭✭

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

• ✭✭✭✭✭✭
edited 01/27/21
Options

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

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

cheers,

Kelly

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!