AVG Collect adding Month and Year

Hello All,

I am looking to average a Likert scale question of 2 hospitals together, from my list of 9 hospitals. How do I add the month and year so I only average the score for January 2024

=AVG(COLLECT({Question 1}, {Hospital 2}, "Hospital A", {Hospital 2}, "Hospital B"))

I have done this in the past, AND(IFERROR(MONTH(@cell, 0) = 1, IFERROR(YEAR(@cell), 0) = 2024)

but it isn't working. This is the first time I have done and =AVG(COLLECT maybe that part is not correct.

Any advice would be appreciated,




  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭

    So have you tried it without the AND statement?

    =AVG(COLLECT({Question 1}, {Hospital 2}, "Hospital A", {Hospital 2}, "Hospital B", {DATE COLUMN}, IFERROR(MONTH(@cell), 0 ) = 1))

    The DATA COLUMN being whatever you have named your date column.

  • Lori C
    Lori C ✭✭

    I'll try that.

    Thank you,


  • Lori C
    Lori C ✭✭

    It did not work.

    Here is the data and my my data collection sheet I want to average Question 1 for 2 hospitals out of the 9, I collect data on for January 2024, then again in February and so on. I have tried several things without success any help would be appreciated.

  • Lori C
    Lori C ✭✭

    When I tried the formula below it was #unparseable

    =AVG(COLLECT({Question 1}, {Hospital}, "Hospital A", {Hospital}, "Hospital B", {Date Column}, IFERROR(MONTH(@cell), 0 ) =1, IFERROR(YEAR(@cell), 0) = 2024)

  • Lori C
    Lori C ✭✭

    =AVG(COLLECT({Question 1}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024), {Hospital 2}, "Hospital A"))

    I got the above formula to work for 1 hospital but not combing the data for 2 hospitals.

    How do I combine the data from hospital A and B?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!