获取所有巧合作为 SUMIF 的标准

Question

-> PAYMENT METHODS TABLE   
NAME SCORE
A    4
B    5
C    6

-> BOOKINGS TABLE
ID DATE   CLIENT   PAYED WITH   PRICE
1  10APR  JOHN     A            500
2  10JUN  PATRICK  B            1000
3  10JUL  JOHN     A            300
4  15JUL  JOHN     C            500

我有两个这样的表，基本上我需要的是能够根据几个条件（日期、客户、价格）匹配预订表，然后从所有匹配项中获取“付款方式”名称并在“付款方式表”中加总其各自的分数。这意味着我需要获取一个“付款方式名称”数组（来自匹配的预订），并将其用作 SUMIF 函数中的条件。

我尝试使用这个：

=SUM(SUMIF(PAYMENT_METHODS_TABLE[NAME],INDEX(BOOKINGS_TABLE[PAYED_WITH],MATCH(1,(BOOKINGS_TABLE[CLIENT]:BOOKINGS_TABLE[CLIENT]="JOHN")*(BOOKINGS_TABLE[DATE]:BOOKINGS_TABLE[DATE]>="10/06/2020"),0)),PAYMENT_METHODS_TABLE[SCORE]))

使其成为数组公式 CTRL+SHIFT+ENTER 但它不起作用，MATCH 函数仅返回第一个“付款方式名称”，而不是全部

我想要实现的公式（使用与上面相同的标准 - 客户名称：John 且日期大于或等于“10/06/2020”）应如下所示：

=SUM(SUMIF(PAYMENT_METHODS_TABLE[NAME],{"B","C"},PAYMENT_METHODS_TABLE[SCORE]))

是否可以？

Answer 1

使用 SUMPRODUCT 和一些布尔值：

=SUMPRODUCT((SUMIF(PAYMENT_METHODS_TABLE[NAME],BOOKINGS_TABLE[PAYED WITH],PAYMENT_METHODS_TABLE[SCORE]))*(BOOKINGS_TABLE[CLIENT]="JOHN")*(BOOKINGS_TABLE[DATE]>=DATE(2020,6,10)))

---

如果有动态数组公式FILTER()

=SUM(SUMIF(PAYMENT_METHODS_TABLE[NAME],FILTER(BOOKINGS_TABLE[PAYED_WITH],(BOOKINGS_TABLE[CLIENT]="JOHN")*(BOOKINGS_TABLE[DATE]>="10/06/2020")),PAYMENT_METHODS_TABLE[SCORE]))

---

要创建列表，我们需要做一个复杂的公式来获取数组：

INDEX(BOOKINGS_TABLE[PAYED WITH],N(IF({1},MODE.MULT(IF((BOOKINGS_TABLE[CLIENT]="John")*(BOOKINGS_TABLE[DATE]>=DATE(2020,6,10)),(ROW(BOOKINGS_TABLE[ID])-ROW(BOOKINGS_TABLE[[#Headers],[ID]]))*{1,1})))))

所以：

=SUM(SUMIF(PAYMENT_METHODS_TABLE[NAME],INDEX(BOOKINGS_TABLE[PAYED WITH],N(IF({1},MODE.MULT(IF((BOOKINGS_TABLE[CLIENT]="John")*(BOOKINGS_TABLE[DATE]>=DATE(2020,6,10)),(ROW(BOOKINGS_TABLE[ID])-ROW(BOOKINGS_TABLE[[#Headers],[ID]]))*{1,1}))))),PAYMENT_METHODS_TABLE[SCORE]))

这是一个数组公式，根据版本的不同，退出编辑模式时需要使用 Ctrl-Shift-Enter 而不是 Enter 进行确认。