SUMPRODUCT с VLOOKUP

Question

Я пытаюсь получить продукт и сумму совпадений в столбце TC Ref и типе Earn Type.

Например, 7926 будет (45,32 x -3) + (45,32 x -3) + (45,32 x -4) для REG и (45,32 x -4) для OT.

Я попробовал SUMPRODUCT с VLOOKUP, но получил только первую подходящую строку. У меня есть другая таблица, которой я пытаюсь вернуть значение.

=SUMPRODUCT(VLOOKUP([@[TC Ref]],Table_timecard,2,FALSE),VLOOKUP([@[TC Ref]],Table_timecard,3,FALSE))

Текущая формула: (Есть ли лучший способ сделать это?)

=SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="REG"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)

Объединить формулу: (Есть ли способ объединить J2: J5574, чтобы найти все типы REG, ADDTL, FMHOL, SHIFT)

=SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="REG"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)+SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="ADDTL"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)+SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="FMHOL"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)+SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="SHIFT"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)

Пример данных:

TC Ref Pay Rate     Hours   Earn Type

7926     $45.32     -3      REG
7926     $45.32     -3      REG
7926     $45.32     -4      OT
7927     $45.32      3      REG
7927     $45.32      7      REG
7927     $45.32      3      DT
7927     $45.32      3      OT

Answer 1

Я мог бы заставить его работать, используя вспомогательный столбец, как показано ниже

TC Ref  Pay Rate    Hours   Value   Earn Type
7926    45.32          -3   -135.96 REG
7926    45.32          -3   -135.96 REG
7926    45.32          -4   -181.28 OT
7927    45.32           3   135.96  REG
7927    45.32           7   317.24  REG
7927    45.32           3   135.96  DT
7927    45.32           3   135.96  OT

В конце концов, если вы перечислите уникальные значения в столбце TC Ref, такие как

7926  -453.2  
7927   725.12

На приведенном выше рисунке показаны формулы, используемые в отношении каждого из TC Ref.

Answer 2

Вы можете использовать одну формулу SUMPRODUCT как это

=SUMPRODUCT((Timecard!$A$2:$A$5574=[@[TC Ref]])*ISNUMBER(MATCH(Timecard!$J$2:$J$5574,{"REG","ADDTL","FMHOL","SHIFT"},0)),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)

Функция MATCH сопоставляет J2:J5574 с "константой массива", содержащей ваши 4 значения - если есть совпадение, то MATCH возвращает число, в противном случае вы получите ошибку # N/A - функция ISNUMBER затем преобразует ее в TRUE или FALSE которые мы можем использовать в SUMPRODUCT при умножении на ваше первое условие