-1

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 ?

1 Answer 1

2

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

enter image description here

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)

enter image description here

2
  • 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.
    – patkim
    Nov 14, 2017 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.