AVERAGEIF & INDEX/LOOKUP formula for the criteria range

[manueltnascimento]

Hi guys, first post ever

I need to calculate the average of a range of values depending on criteria (thus using AVERAGEIF or similar).
The thing is that the 'criteria_range' isn't next to the 'average_range' and I can't make it work with an INDEX&MATCH inside the AVERAGEIF formula.

»» I need to calculate the average value of a list of values depending on the product family, but I don't have the product family on the main table (i.e. where I do have the values).

»» Imagine I want to find the average for the Family A products of the Sheet1 Table below. How to do it without bringing the Family column to the main table?

Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1: SKU_column[/TD]
[TD]...[/TD]
[TD]E1: Values_column[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]...[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]...[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]...[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]...[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1: SKU_column[/TD]
[TD]B1: Prod_Family[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Family A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Family B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Family A[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Family C[/TD]
[/TR]
</tbody>[/TABLE]


Thanks a lot for your time and sorry if I'm sounding confused!

Manuel

 

[Aladin Akyurek]

What is the expected average for "the Family A products"?

 

[manueltnascimento]

What is the expected average for "the Family A products"?

In this case it would be 3 ((6+2)/2).

One important thing: both tables are neither of the same size or order.

Thanks!
MN

 

[Aladin Akyurek]

In this case it would be 3 ((6+2)/2).

One important thing: both tables are neither of the same size or order.

Thanks!
MN

Great.

In G2 of Sheet1 enter: Family A

In H2 of Sheet1 control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH($A$2:$A$5,IF(Sheet2!$B$2:$B$5=G2,Sheet2!$A$2:$A$5),0)),$E$2:$E$5))

 

[manueltnascimento]

Great.

In G2 of Sheet1 enter: Family A

In H2 of Sheet1 control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH($A$2:$A$5,IF(Sheet2!$B$2:$B$5=G2,Sheet2!$A$2:$A$5),0)),$E$2:$E$5))

Perfect! It works!

Thanks a lot! Very helpful!

Manuel

 

[Aladin Akyurek]

Perfect! It works!

Thanks a lot! Very helpful!

Manuel

You are welcome.