Rolling 12 Months using AVG COLLECT formula help


Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help.

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!