AVERAGEIFS + OR + One More Criteria....

[alexx579]

Hi,

In Row A, let's say I have a either of the listed:
Company A
Company B
Company C
Company D

In Row B, let's say I have a either of the listed:
Product 1
Product 2
Product 3
Product 4

In Row C, I have a bunch of random prices listed.

How can I create a formula where I want the average of both Company A OR Company B for Product 1? I want one average price no matter if it is company A or B, for Product 1. How do I do that?? THANKS!!! I am gonna want average for combinations of companies and products, the actual data is 12 deep on companies and 13 on products.

 

[Eric W]

Try:

=SUM(SUMIFS(C:C,A:A,{"Company A","Company B"},B:B,"Product 1"))/SUM(COUNTIFS(A:A,{"Company A","Company B"},B:B,"Product 1"))


If you want to put "Company A" and "Company B" in F2:F3 so that you can easily change them without having to change the formula, try:

=SUMPRODUCT(SUMIFS(C:C,A:A,F2:F3,B:B,"Product 1"))/SUMPRODUCT(COUNTIFS(A:A,F2:F3,B:B,"Product 1"))

 

[Eric W]

This version should let you choose the companies and products you want to include:

	A	B	C	D	E	F	G	H	I
1	Company	Product	Amount		Company	Product		Average
2	A	1	1		A	1		4.6
3	B	1	2		B	4
4	C	2	3		C	3
5	D	2	4
6	A	1	5
7	B	2	6
8	C	1	7
9	A	1	8
10	X	3	9

<tbody>
</tbody>

Sheet7​

Array Formulas Cell Formula H2 {=SUM(SUMIFS(C:C,A:A,E2:E15,B:B,TRANSPOSE(F2:F15)))/SUM(COUNTIFS(A:A,E2:E15,B:B,TRANSPOSE(F2:F15)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the companies in column E, products in F.

 

[alexx579]

Worked great, thanks Eric!!!

 

[Eric W]

Glad to help!