SUMPRODUCT com o VLOOKUP

Question

Estou tentando obter o Produto e a Soma de quais correspondências na coluna TC Ref e no tipo de ganho.

Por exemplo, 7926 seria (45,32 x -3) + (45,32 x -3) + (45,32 x -4) para REG e (45,32 x -4) para OT.

Eu tentei um SUMPRODUCT com um VLOOKUP, mas obtive apenas a primeira linha correspondente. Eu tenho outra tabela que estou tentando retornar o valor.

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

Fórmula atual: (Existe uma maneira melhor de fazer isso?)

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

Combine Formula: (Existe uma maneira de combinar o J2: J5574 para encontrar todos os tipos 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)

Exemplo de dados:

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

Você pode usar uma única fórmula SUMPRODUCT como esta

=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)

A função

MATCH corresponde a J2:J5574 em relação a uma "constante de matriz" contendo seus 4 valores - se houver uma correspondência, MATCH retornará um número, caso contrário, você receberá o erro # N / A - a função ISNUMBER converterá para TRUE ou FALSE que podemos usar em SUMPRODUCT quando multiplicado pela sua primeira condição

    

Answer 2

Eu poderia fazê-lo funcionar usando uma coluna auxiliar, como mostrado abaixo

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

No final, se você listar os valores exclusivos na TC Ref col, como

7926  -453.2  
7927   725.12

A figura acima mostra as fórmulas usadas contra cada uma das TC Ref.