AverageIFs with both vertical and horizontal criterias

[cacos]

Hi everyone! Is anyone aware of a way of averaging while using both vertical and horizontal criterias?


For example, one criteria might be in A:A, while the next in D1:D20.


Thanks!

 

[Luke M]

Lengthy, but this setup will work. Assuming this layout:

[pre]

Ex.	cat	dog	cat
a	3	4	10
a	2	5	7
b	7	9	5
b	3	9	7

[/pre]
Average of values from Group "a" that are in cat columns:

=SUMPRODUCT((C3:E6)*(B3:B6="a")*(C2:E2="cat"))/SUMPRODUCT(1*(((C3:E6)*(B3:B6="a")*(C2:E2="cat"))>0))


Answer: 5.5

 

[cacos]

Thanks, it works perfectly!