AVERAGEIF value between a date range



I am creating a metrics sheet that cross references an archive sheet and am looking to pull statistics within monthly ranges.

I want to return a monthly average of the Total ADL Score within a monthly range which is determined by the ARD (Due) Date column. This data is returned to my metrics sheet (Sheet name: Avon Archive Case Mix Metrics). For example, I need the Average ADL Score in January, February, March and so on.

This is my formula and I am receiving an #INCORRECT ARGUMENT SET error.

=AVERAGEIF({Archive Avon CMI Weekly Meeting Form Range 1}, >=

DATE(2022, 1, 1), {Archive Avon CMI Weekly Meeting Form Range 1},

<=DATE(2022, 1, 31), {Archive Avon CMI Weekly Meeting Form Range 3})

I am referencing a data range (Range 1) and a number column (Range 3)

Here are some screen shots:

Range 1 is ARD (Due) Date

Range 3 is Total ADL Score

@Genevieve P.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!