SUMIFS с несколькими критериями

Question

Основываясь на Emp ID и дате окончания выплаты, я собираюсь подвести итоги общего заработка, и, если он равен или превышает лимит WC в столбце экспозиции, я хотел бы вернуть значение Cap WC. Если он меньше, чем ограничение WC, я хотел бы вернуть сумму совокупного дохода для соответствующего Emp ID и даты окончания выплаты в столбце подверженности.

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

 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 

Пример результатов:

 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

Есть ли способ также просто вернуть значение в последнем ряду? Ex COR72 имеет 3 записи на 01.04.2014, может 1-я и 2-я строка быть нулевыми, а 3-я строка имеет значение.

Answer 1

Если ваши данные отсортированы по Pay End и Emp ID , вы можете заполнить следующую формулу в поле Exposure (начиная с 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))

Эта формула работает, проверяя, соответствует ли приведенный ниже идентификатор сотрудника идентификатору сотрудника в текущей строке. Если это так, вводится 0 . Если нет, то строка является последней записью для этого идентификатора и даты.

Значение cap в столбце D обрабатывается с использованием суммы двух функций SUMIFS : одна для суммирования соответствующих значений в столбце C, где значение в C меньше значения cap в столбце D, и другая для суммирования соответствующих значений в D где значение в столбце C больше или равно значению в D. Это гарантирует, что правильное значение подсчитывается для каждой записи.