Сумма строки от текущей ячейки к определенной ячейке и только для определенных меток

Question

Пример:

общая сумма отдела P1 составляет 200

общая сумма отдела P2 составляет 500

общая сумма отдела P3 составляет 100

Code Cost Remaining
P1    20   180
P2    50   450
P3    30    70
P1    30   150
P2    20   430 
P1    10   140 

Как видите, я хочу вычесть стоимость из общей суммы для каждой строки и отобразить оставшуюся сумму в этой строке.

Как я могу достичь этого?

Answer 1

Попробуйте это решение и подтвердите, работает ли оно для вас. Я предполагаю, что у вас есть только 3 отдела, как показано в вашем вопросе. Если у вас много других отделов, формула будет слишком сложной для обслуживания и может не сработать. Может быть желательным другое решение.

В этом примере ваши данные отдела находятся в ячейках C3:D8. Общая сумма для каждого отдела (P1, P2 и P3) находится в ячейках D1, E1 и F1 соответственно.

В ячейку E1 поместите следующую формулу и нажмите CTRL+SHIFT+ENTER, чтобы создать формулу массива из панели формул. Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива. Перетащите его вниз до предполагаемых клеток.

=IF(C3="P1",$D$1-SUM(IF($C$3:C3="P1",$D$3:D3)),IF(C3="P2",$E$1-SUM(IF($C$3:C3="P2",$D$3:D3)),IF(C3="P3",$F$1-SUM(IF($C$3:C3="P3",$D$3:D3)))))

Обновить

Если у вас есть еще много отделов, примите следующее решение. Создайте таблицу отделов и максимальных значений. В этом примере это в H1:I6.

Пример данных в С3:D9. В ячейку E3 поместите следующую формулу и перетащите ее вниз.

=VLOOKUP(C3,$H$1:$I$6,2)-SUMIFS($D$3:D3,$C$3:C3,C3)