# Sum of row from current cell to specific cell and only of particular labels

Example:

total amount of P1 department is 200

total amount of P2 department is 500

total amount of P3 department is 100

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

As you can see i want to subtract cost from total for each row and display the remaining amount in that row.

How can i achieve this ?

Try this solution and confirm if it works for you. I assume you have only 3 departments as shown in your question. If you have many more departments the formula shall be too difficult to maintain and may not work. A different solution may be desired.

In this example, your Department Data is in Cells C3:D8. The Total amount for each department (P1,P2 & P3) is in cells D1, E1 & F1 respectively.

In Cell E1 put the following formula and press CTRL+SHIFT+ENTER to create an Array Formula from within the Formula Bar. The formula shall be enclosed in Curly Braces to indicate that it's an Array Formula. Drag it down up to the intended cells.

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

## Update

If you have many more departments adopt a following solution. Create a table of Departments and Max Values. In this example it's in H1:I6.

Sample data in C3:D9. In Cell E3 put the following formula and drag it down.

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

• Can you give me some generalized solution as i have many more departments not just 3...so writing IFs for each department would be very tedious.
– Raj
Nov 14, 2017 at 9:54
• Answer updated. Do try and confirm. Nov 14, 2017 at 10:16