# AVERAGEIF and Quarters

Options

Hello,

I have two sheets:

Sheet 1 - contains a column with "PROD Release Dates" and a 2nd column that contains a formula to calculate the "Cycle Time Months" it took to complete the work.

Sheet 2 - contains 4 rows to show the Quarter start/end dates for 2023. You will see this is referenced as "[Quarter Date End]@row" in the formula below.

Formula Issue: I want to use AVERAGEIF to reference Sheet 1 and any date that falls within Q1, Q2, Q3, Q4, then I want to average the Months column.

The below formula doesn't work.

=AVERAGEIF({Cycle Time Months}, {PROD Release Date} <= [Quarter Date End]@row)

Thank you!

Amy

• ✭✭✭✭✭✭
Options

Hey @Amy Evans

The syntax smartsheet is expecting for an AverageIF is AVERAGEIF(range, criteria, [average_range]), where the first range is part of the range-criteria pair, and the last range is what needs to be averaged, if it is a different range than the first. Note an AverageIf will only support one criteria. If there are more criterion required to filter the data, one must swap to AVG(COLLECT). The AverageIf works for your case.

AVERAGEIF({PROD Release Date},@cell<=[Quarter Date End]@row, {Cycle Time Months})

Does this formula give you the expected result?

Kelly

• ✭✭✭✭✭✭
Options

Hey @Amy Evans

The syntax smartsheet is expecting for an AverageIF is AVERAGEIF(range, criteria, [average_range]), where the first range is part of the range-criteria pair, and the last range is what needs to be averaged, if it is a different range than the first. Note an AverageIf will only support one criteria. If there are more criterion required to filter the data, one must swap to AVG(COLLECT). The AverageIf works for your case.

AVERAGEIF({PROD Release Date},@cell<=[Quarter Date End]@row, {Cycle Time Months})

Does this formula give you the expected result?

Kelly

• Options

Thank you! Your suggested formula worked!!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!