# Find Average Number if Criteria is Met

Options
✭✭✭✭✭

I need to determine the AVG number from a list of numbers if the Year and Week Number criteria are met.

For example, if the Year is 2025 (Year Column) and the Week Number is 1 (Week Number Column), I need the average number of days in the FO Node Upgrades Duration column.

I know that this formula =COUNTIFS({Year}, "2025", {Week Number}, "1" will narrow down the count to Year and Day of Week. But I'm not sure how to get the AVG number from the list in the FO Node Upgrades Duration column.

Thank you,

Mike Tuccillo

Tags:

• ✭✭✭✭✭✭
Options

Try this:

=AVG(COLLECT({Duration}, {Year}, @cell = 2025, {Week Number}, @cell = 1))

• ✭✭✭✭✭✭
Options

Try this:

=AVG(COLLECT({Duration}, {Year}, @cell = 2025, {Week Number}, @cell = 1))

• ✭✭✭✭✭
Options

That did it! Thank you

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
Options

Hi, I need to collect Average (of 'Days Since Created') if the 'Priority' is 'Critical'. How can I use Collect to do that? Thanks

• ✭✭✭✭✭✭
Options

@nualamcd You would use a similar syntax, but you would only have one range/criteria set.

=AVG(COLLECT({Range to average}, {Criteria Range}, criteria))

• ✭✭
Options

OK, seems we got it to work! Thank you :-)

It just doesn't look like the others because the range doesn't have : between... But glad it's working!

=AVG(COLLECT({Days Since Created}, {Priority}, [Column2]\$21))

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!