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:

A1: SKU_column	...	E1: Values_column
A	...	2
B	...	3
C	...	4
D	...	5

<tbody>
</tbody>

Sheet2:

A1: SKU_column	B1: Prod_Family
A	Family A
B	Family B
C	Family A
D	Family C

<tbody>
</tbody>

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.