INDEX/MATCH with AVERAGEIF

[dunmore83]

Hi,

In 'Heifers-Steers-By-Day' worksheet I have these variables;

Rows T20:NT20 have dates from 1/7/16 to 30/6/17

Range T24:NT1202 have numerical values

In cell J36 (separate worksheet) I need a formula to match the date in cell G36 (separate worksheet; same sheet as J36) with T20:NT20 & then return the average of this column (i.e. T24:T1202) for all values greater than 0.

Any helpers ??

 

[Marcelo Branco]

Try

=AVERAGEIF(INDEX('Heifers-Steers-By-Day'!$T$24:$NT$1202,0,MATCH(G36,'Heifers-Steers-By-Day'!$T$20:$NT$20,0)),">0")

Hope this helps

M.

 

[dunmore83]

Thanks!

I actually need to add 2 conditions to the above formula;

'Heifers-Steers-By-Day'!$G$24:$G$1202,">="&Y36
'Heifers-Steers-By-Day'!$G$24:$G$1202,"<="&Y36

Any ideas ??

 

[Marcelo Branco]

Try AVERAGEIFS with INDEX/MATCH to get the proper column (see post#2)
AVERAGEIFS function - Office Support

M.