locked
averageifs with calculated criteria RRS feed

  • Question

  • Hello,

    I'm trying to write a formula to calculate an average value of data retrieved by web query. I want to calculate an average value of specific month, specific year and on cells that are no empty, e.i. based on three criteria. The date is in column C and data is in column D.

    An array formula like this do the job..

    {=AVERAGE(IF((MONTH($C$21:$C$710)=MONTH(C13))*(YEAR($C$21:$C$710)=YEAR(C13))*($D$21:$D$742<>"");$D$21:$D$742;""))}

    I would like to do the same by using SUMIFS function.

    I tried this -

    AVERAGEIFS($D$21:$D$710;$C$21:$C$710;month($C$21:$C$710)=11;$C$21:$C$710;year($C$21:$C$710)=2011;$C$21:$C$710;<>"")

    but somehow Excel does no even accept it. Could someone help me write this formula.

    Thanks a lot.

     

    • Edited by yuri-b Wednesday, November 23, 2011 10:57 AM
    Wednesday, November 23, 2011 10:44 AM

Answers

  • COUNTIFS, SUMIFS and AVERAGEIFS only work with criteria that apply to the criteria ranges, such as "=3" or ">"&$A$1 etc. You can't use a complete condition such as month($C$21:$C$710)=11. You'll have to keep on using your original array formula, or add calculated columns to your data that compute the month and year of the dates in column C, and use these calculated columns in AVERAGEIFS.


    Regards, Hans Vogelaar
    • Marked as answer by yuri-b Wednesday, November 23, 2011 12:53 PM
    Wednesday, November 23, 2011 11:19 AM

All replies

  • COUNTIFS, SUMIFS and AVERAGEIFS only work with criteria that apply to the criteria ranges, such as "=3" or ">"&$A$1 etc. You can't use a complete condition such as month($C$21:$C$710)=11. You'll have to keep on using your original array formula, or add calculated columns to your data that compute the month and year of the dates in column C, and use these calculated columns in AVERAGEIFS.


    Regards, Hans Vogelaar
    • Marked as answer by yuri-b Wednesday, November 23, 2011 12:53 PM
    Wednesday, November 23, 2011 11:19 AM
  • Thank you for helping.

    The reason I was trying to use averageifs instead of array formula was that an array formula did not always updated ranges upon web query update. However after more detailed investigation I found that problem lies elsewhere.

    Wednesday, November 23, 2011 12:56 PM