SUMIFS w / multiple Criteria

Question

Baseie-se no ID Emp e na data final do pagamento Estou procurando somar o Ganho Total e, se for igual ou maior que o limite do WC na coluna de exposição, eu gostaria de retornar o valor do WC Cap. Se for menor que o limite de WC, eu gostaria de retornar a soma do total de ganhos para a data correspondente de ID de emp e de final de pagamento na coluna de exposição.

Dados da amostra:

 Pay End     Emp ID   Total Earnings   WC (Cap)
 04/01/2014  CIA77    $1,858.80        $1,212.98 
 04/01/2014  COL23    $1,375.73        $1,212.98 
 04/01/2014  COM64      $285.20        $1,212.98 
 04/01/2014  COR71    $2,821.72        $1,212.98 
 04/01/2014  COR72      $732.35        $1,212.98
 04/01/2014  COR72    $ (732.35)       $1,212.98
 04/01/2014  COR72      $272.10        $1,212.98
 04/01/2014  COR73      $460.25        $1,212.98 
 04/01/2014  COS83    $2,239.20        $1,212.98 

Resultados da amostra:

 Pay End     Emp ID   Total Earnings   WC (Cap)     Exposure
 04/01/2014  CIA77    $1,858.80        $1,212.98    $1,212.98
 04/01/2014  COL23    $1,375.73        $1,212.98    $1,212.98
 04/01/2014  COM64      $285.20        $1,212.98      $285.20 
 04/01/2014  COR71    $2,821.72        $1,212.98    $1,212.98 
 04/01/2014  COR72      $732.35        $1,212.98        $0.00
 04/01/2014  COR72    $ (732.35)       $1,212.98        $0.00
 04/01/2014  COR72      $272.10        $1,212.98      $272.10
 04/01/2014  COR73      $460.25        $1,212.98      $460.25
 04/01/2014  COS83    $2,239.20        $1,212.98    $1,212.98

Existe uma maneira de simplesmente retornar o valor na última linha? Ex COR72 tem 3 entradas em 4/1/2014 pode a 1ª e 2ª linhas serem zero e a 3ª linha ter o valor.

    

Answer 1

Se os dados forem classificados por Pay End e Emp ID , você poderá preencher a seguinte fórmula no campo Exposure (iniciando em E2 ).

=IF(B2=B3,0,SUMIFS($C$2:$C$10,$B$2:$B$10,B2,$A$2:$A$10,A2,$C$2:$C$10,"<"&D2)+SUMIFS($D$2:$D$10,$B$2:$B$10,B2,$A$2:$A$10,A2,$C$2:$C$10,">="&D2))

Essa fórmula funciona verificando se o ID do funcionário abaixo corresponde ao ID do funcionário na linha atual. Nesse caso, um 0 é inserido. Caso contrário, a linha é a última entrada para esse ID e data.

O valor de limite na coluna D é manipulado usando a soma de duas funções SUMIFS : uma para somar os valores apropriados na coluna C, em que o valor em C é menor que o valor de limite na coluna D e outro para somar os valores apropriados em D, em que o valor na coluna C é maior ou igual ao valor em D. Isso garante que o valor correto seja contado para cada registro.