Average if date falls in a month and contains certain text

Still learning average formulas! Trying to come up with a formula to average the number of items where the despatch date is in a certain month (ie January - not of a particular year but all January's) and if another column contains certain text. So it will average the number of items in January if the country column is Asia/Pacific.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are first going to need to insert a text/number column (called "Year" in this example) into the sheet containing the source data and use this column formula:

    =IFERROR(YEAR([Date Column Name]@row), "")

    Then in the sheet where you want your averages, we would use this:

    =IFERROR(COUNTIFS({Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1) / COUNT(DISTINCT(COLLECT({Source Sheet Year Column}, {Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1))), 0)

    The above will work for January. For other months, simply change both 1s to the appropriate month number you are wanting to get the average for.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!