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
Company	Product	Amount	Company	Product	Average
A
B	B
C	C
A
B
C
A
X

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=SUM(SUMIFS(C:C,A:A,E2:E15,B:B,TRANSPOSE(F2:F15)))/SUM(COUNTIFS(A:A,E2:E15,B:B,TRANSPOSE(F2:F15)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Put the companies in column E, products in F.

 

[alexx579]

Worked great, thanks Eric!!!

 

[Eric W]

Glad to help!