AVERAGEIF value between a date range

Hello,

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

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭
    Answer ✓

    @Paula Meunier

    Try the below:

    =AVERAGEIF({Archive Avon CMI Weekly Meeting Form Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)), {Archive Avon CMI Weekly Meeting Form Range 3})

Answers