I'm trying to calculate an average # days an account has been open if all 3 specific conditions are met.

Average range: R6:R102


  • A6:A102 = "x"
  • S6:S102 = "* EWS *" (contain part of EWS)
  • B6:B102 = past 3 years For last condition, the easiest solution was to put a formula in A3 (where A3= =TODAY()-1095) and then reference it in my AVERAGEIFS formula.

=AVERAGEIFS(R6:R102, A6:A102, "x", S6:S102, "*EWS*", B6:B102, ">A3")

Formula worked fine until I added condition #3. Now, I get a DIVIDE BY ZERO error. Why does it give me an error? Should I be making the last condition a date between today and 3yrs ago?

appreciate any insight you can provide. thank you!

  • 1
    Try ">"&A3 as last condition. Aug 17 '18 at 19:28
  • @JacobJanTuinstra, this works! user0 also came up with a solution that got rid of using cell A3. both solutions work great. thank you! Aug 17 '18 at 21:14

=TODAY()-1095 doesn't count for leap years etc. so:

=AVERAGEIFS(R4:R102, A4:A102, "x", 
                     S4:S102, "*EWS*", 
                     B4:B102, ">"&MONTH(TODAY())&"/"&DAY(TODAY())&"/"&YEAR(TODAY())-3)
  • you're the best, @user0! thank you yet again :) Aug 17 '18 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.