Help Understanding AVERAGEIF

[rtroehrig]

I'm trying to pull an average on Sheet 2 from columns C:F on Sheet1 based on a match in column A on Sheet 1 and column B on Sheet 2. I thought this should be a simple AVERAGEIF formula. I entered the formula as follows:

=AVERAGEIF('Sheet1'!A:A,'Sheet2'!B:B,'Sheet1'!C:F)

However, I am not receiving the value I should be.

Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1
[/TD]
[TD]1/1/18
[/TD]
[TD]1074[/TD]
[TD][/TD]
[TD][/TD]
[TD]444[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]1/2/18[/TD]
[TD][/TD]
[TD]506[/TD]
[TD]895[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]1/3/18[/TD]
[TD][/TD]
[TD]428[/TD]
[TD]697[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]1/4/18[/TD]
[TD]603[/TD]
[TD][/TD]
[TD][/TD]
[TD]252[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]1/5/18[/TD]
[TD]712[/TD]
[TD][/TD]
[TD][/TD]
[TD]590[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]1/6/18[/TD]
[TD][/TD]
[TD]416[/TD]
[TD]795[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]1/7/18[/TD]
[TD][/TD]
[TD]964[/TD]
[TD]735[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]1/8/18[/TD]
[TD][/TD]
[TD]258[/TD]
[TD]737[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]1/9/18[/TD]
[TD]811[/TD]
[TD][/TD]
[TD][/TD]
[TD]536[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]1/10/18[/TD]
[TD]514[/TD]
[TD][/TD]
[TD][/TD]
[TD]749[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]1/11/18[/TD]
[TD][/TD]
[TD]351[/TD]
[TD]649[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2[/TD]
[TD]1/12/18[/TD]
[TD][/TD]
[TD]732[/TD]
[TD]408[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2[/TD]
[TD]1/13/18[/TD]
[TD]1009[/TD]
[TD][/TD]
[TD][/TD]
[TD]495[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Week[/TD]
[TD]Average (calculated)[/TD]
[TD]Average(Formula)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/18-1/7/18[/TD]
[TD]1
[/TD]
[TD]651[/TD]
[TD]796[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/8/18-1/14/18[/TD]
[TD]2[/TD]
[TD]583[/TD]
[TD]671
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Averageif is not using the right average_range

Try

=(SUMIF(Sheet1!$A$2:$A$14,B2,Sheet1!$C$2:$C$14)+SUMIF(Sheet1!$A$2:$A$14,B2,Sheet1!$D$2:$D$14)+SUMIF(Sheet1!$A$2:$A$14,B2,Sheet1!$E$2:$E$14)+SUMIF(Sheet1!$A$2:$A$14,B2,Sheet1!$F$2:$F$14))/(SUMPRODUCT(--(Sheet1!$A$2:$A$14=B2),--(Sheet1!$C$2:$C$14<>""))+SUMPRODUCT(--(Sheet1!$A$2:$A$14=B2),--(Sheet1!$D$2:$D$14<>""))+SUMPRODUCT(--(Sheet1!$A$2:$A$14=B2),--(Sheet1!$E$2:$E$14<>""))+SUMPRODUCT(--(Sheet1!$A$2:$A$14=B2),--(Sheet1!$F$2:$F$14<>"")))

 

[Aladin Akyurek]

In C2 of Sheet2, control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(Sheet1!$A$2:$A$14=$B2,IF(ISNUMBER(Sheet1!$C$2:$F$14),Sheet1!$C$2:$F$14)))

 

[rtroehrig]

In C2 of Sheet2, control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(Sheet1!$A$2:$A$14=$B2,IF(ISNUMBER(Sheet1!$C$2:$F$14),Sheet1!$C$2:$F$14)))

This was perfect! Thank you very much.

 

[Aladin Akyurek]

This was perfect! Thank you very much.

You are welcome.