Dynamic Averageifs between multiple spreadsheets

  • Long time viewer, first time poster here and I am trying to make a rolling 3 month using an averageifs formula. I want this formula to be dynamic within multiple worksheets.


    Currently, I have 3 data tabs all built and formatted the same. In column C (C11-C20) I have the name of different sports. In D8:O8 I have the dates. I also have a summary tab that captures the basic detail. The way I have it set up, you can enter a date in C7 and a state name (name of the worksheet/tab) in C8 and the formula will pull the 3 month rolling average. The formula I currently have that seems to be pulling correctly is:


    =IFERROR(AVERAGEIFS(INDIRECT("'"&$C$8&"'!"&"$D"&ROW($D11)&":$AS"&ROW($D11)),INDIRECT("'"&$C$8&"'!"&"$D"&ROW($D$8)&":$AS"&ROW($D$8)),">="&EDATE($C$7,-2),INDIRECT("'"&$C$8&"'!"&"$D"&ROW($D$8)&":$AS"&ROW($D$8)),"<="&$C$7),"")


    I am trying to make my formula more dynamic - the averageifs formula right now is calculating based on what is in the corresponding row (i.e. basketball is row 11 on the summary tab and the data tabs). Since I am making a summary tab - I dont need to capture every single metric on my data tabs (i.e. what if basketball was in row 20). I believe I need to incorporate some sort of match function within my first indirect, but I havent had any luck.


    I realize the easiest way to do this is map the corresponding row to my formula, however please note that this is just dummy data. The file I am dealing with has 2000 rows on the data tab and I cant map each formula individually.


    I have attached a copy. Thanks in advance
    [ATTACH=CONFIG]58380[/ATTACH]

  • Re: Dynamic Averageifs between multiple spreadsheets


    Try:


    =IFERROR(AVERAGEIFS(INDEX(INDIRECT("'"&$C$8&"'!D11:AS20"),MATCH($C11,INDIRECT("'"&$C$8&"'!C11:C20"),0),0),INDIRECT("'"&$C$8&"'!"&"D8:AS8"),">="&EDATE($C$7,-2),INDIRECT("'"&$C$8&"'!"&"D8:AS8"),"<="&$C$7),"")


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!