AVERAGEIFS formula

[redspanna]

Hi all

I have this formula that will give me the average all times in the LOG sheet for those held through column G if the Month value is the same as that in cell T11

=AVERAGEIFS(Log!G:G,Log!A:A,">="&T11,Log!A:A,"<="&EOMONTH(T11,0))

How can this formula be changed so that the average is actually taken from a more than one column in the LOG sheet, for example G:AE?

If I simply change my formula to =AVERAGEIFS(Log!G:AE,Log!A:A,">="&T11,Log!A:A,"<="&EOMONTH(T11,0)) I get a #VALUE error

thanks in advance

 

[RoryA]

All your ranges need to be the same size and shape for AVERAGEIFS so you'll need an array formula something like:

=AVERAGE(IF(Log!A1:A1000>=T11,IF(Log!A1:A1000<=EOMONTH(T11,0),Log!G1:AE1000,""),""))

array-entered with Ctrl+Shift+Enter. Note: do not use entire columns with array formulas.

 

[redspanna]

All your ranges need to be the same size and shape for AVERAGEIFS so you'll need an array formula something like:

=AVERAGE(IF(Log!A1:A1000>=T11,IF(Log!A1:A1000<=EOMONTH(T11,0),Log!G1:AE1000,""),""))

array-entered with Ctrl+Shift+Enter. Note: do not use entire columns with array formulas.

Thanks for suggestion, but the result I'm getting doesn't seem correct. If I highlight all the cells myself and look at the bottom right corner of the sheet, Excel gives me an average of 6:23 whilst the formula gives a result of 1:32 for example

 

[RoryA]

Are there blank cells in your data range? If so, you'll need:

=AVERAGE(IF(Log!A1:A1000>=T11,IF(Log!A1:A1000<=EOMONTH(T11,0),IF(Log!G1:AE1000<>"",Log!G1:AE1000,""),""),""))

 

[redspanna]

Are there blank cells in your data range? If so, you'll need:

=AVERAGE(IF(Log!A1:A1000>=T11,IF(Log!A1:A1000<=EOMONTH(T11,0),IF(Log!G1:AE1000<>"",Log!G1:AE1000,""),""),""))

Perfect - thanks so much