# AVG(COLLECT) Formula with Multiple Criteria, Including Date Range

✭✭✭✭✭

I'm trying to write an AVG(COLLECT) formula on a separate metrics sheet to calculate the average number of days in a month for all rows with:

Priority = High

Polling Question = Yes

Start Date >=1/1/2024

Closed Date <=1/31/2024

I've tried numerous formulas, the latest of which is =AVG(COLLECT({IRI-NumberOfDays}:{IRI-NumberOfDays}, {IRI-Priority}:{IRI-Priority}, @cell = "High", {IRI-PollingQuestion}:{IRI-PollingQuestion}, @cell = "Yes")), which comes back as UNPARSEABLE.

Thanks for any help.

Lori Flanigan

• ✭✭✭✭✭✭

Cross sheet references should only be entered once as a single range.

Same sheet:

[Column name]:[Column name]

Cross Sheet:

{Range Name}

Something to keep in mind is that your date ranges will miss anything that is (for example) opened in December and closed in January or opened in January and closed in February. It will only capture rows that were both opened and closed in the month of January.

• ✭✭✭✭✭✭

Cross sheet references should only be entered once as a single range.

Same sheet:

[Column name]:[Column name]

Cross Sheet:

{Range Name}

Something to keep in mind is that your date ranges will miss anything that is (for example) opened in December and closed in January or opened in January and closed in February. It will only capture rows that were both opened and closed in the month of January.

• ✭✭✭✭✭

Thank you, Paul! Yes, I have mentioned the concern you pointed out; they want to leave it as is for now, anyhow.

Thanks again!

Lori

• ✭✭✭✭✭✭
• ✭✭✭✭✭

Paul, I can't stop thinking about the month crossover. I know it will come up in the future. How would the formula change to accommodate that crossover?

Thanks,

Lori

• ✭✭✭✭✭✭

You would make the start date less than or equal to the end of the month and the end date greater than or equal to the start of the month.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!