Auto substract by comparing the category

**expert40** · 02-12-2012, 04:22 AM

I am having two sheets .Sheet one contains the following data

category Total amount Remaining
category1 1000
category2 2500
category3 600
category4 9000

Sheet2 having

Name category Final
suresh category1 500
ravi category2
rajesh category3
ramesh category4

If i enter 500 in final for category1 then ,my formula should do

Search for the category in sheet1 so now category is category1 so ,I have to populate

totalamount-final=500 in remaining of category column1 in sheet1.
please try to helpExample2.xlsx

**canapone** · 02-12-2012, 05:28 AM

Hi,

(maybe) in Sheet1 in C2 and down:

=B2-SUMIF(Sheet2!B:B,A2,Sheet2!C:C)

Hope it helps

**expert40** · 02-12-2012, 06:54 AM

Hi CANAPONE,
Thanks a lot for your promt solving the thread.
I have a small problem.
If category was not there and assigned and then we should get 0 (i.e if i assign a category5 in sheet2 and assign amount and if it was not) there.,in the same column or other column and if total money was zero after assigning to some users
is there any possibility of throwing error or populating other column with some error text .

Thanks in advance