Excel查找基于多个条件的平均值(多个值)

Question

表：

      Name Price New/Old
      A    10     Y
      A    20     N
      B    35     Y
      B    45     Y
      C    50     N
      C    60     Y

如果我想找到：当名称=A和新/旧=Y时的平均价格：

 IFERROR(AVERAGEIFS($B$2:$B$7, $A$2:$A$7, "A", $C$2:$C$7, "Y"),"") = 10

这很容易。

然而，当名称=A或B，新/旧=Y时，我发现平均价格时陷入了困境。

正确答案应该是：（10+35+45）/3=30

提前感谢！

Answer 1

使用数组的更直接的方法：

=AVERAGE(IF((($A$2:$A$7="A")+($A$2:$A$7="B")+($C$2:$C$7="Y"))=2,$B$2:$B$7))

记住输入一个数组公式保持Shift+Ctrl并点击Enter

Answer 2

可能有一种更简单/更雄辩的方法，但这应该做到：

=(SUMIFS($B$2:$B$7, $A$2:$A$7, "A", $C$2:$C$7, "Y")+SUMIFS($B$2:$B$7, $A$2:$A$7, 
    "B", $C$2:$C$7, "Y"))/(COUNTIFS($A$2:$A$7, "A", $C$2:$C$7, "Y")+
    COUNTIFS($A$2:$A$7, "B", $C$2:$C$7, "Y"))

Answer 3

非数组公式

=SUM(SUMIFS(B2:B7,A2:A7,TRANSPOSE({"A","B"}),C2:C7,"Y"))/
 SUM(COUNTIFS(A2:A7,TRANSPOSE({"A","B"}),C2:C7,"Y"))

西蒙的比更整洁

编辑：巴里评论后

=SUM(SUMIFS(B2:B7,A2:A7,{"A","B"},C2:C7,"Y"))/SUM(COUNTIFS(A2:A7,{"A","B"},C2:C7,"Y"))