correct total column in filtered list

[Tarvirdi]

Dear friends,
I have a list(table in 2007) as bellow
Serial ItemCode Import Export Remain
1 10 20 20
2 20 10 10
3 10 30 40
4 30 10 50
5 20 20 30

note how last column (remain) is calculated (remain=up remain+import-expor)
if i filter (itemcode) for 10 the remain column value not calculated on new
condition (correct calculation but useful for me)
how can I implement last column calculation to get as bellow list?
Serial ItemCode(=10) Import Export Remain
1 10 20 20
3 10 30 50

Thanks
Tarvirdi

 

[KC Rippstein]

Your spreadsheet design here is very odd. You may want to revisit your
logic setup.
- First, it might be better to record exports as negative numbers since they
are items being removed from your inventory.
- Second, keeping a "remaining inventory" of each item separately seems more
logical. It appears by your filter example below that is exactly what you
are trying to accomplish anyway.
- Third, I would have to imagine that dates would be a useful function to
have.

Bottom line, your layout and design is not making much sense from an outside
perspective, but I think we can get you the answer you are looking for, just
not in the way you are asking for it.

I would suggest the following formula for column F (which will give you
inventory of just that item code if you take my suggestion and report column
D as negative numbers):
=SUMIF($B$2:$B2,$B2,$C$2:$D2)

If you insist on exports being positive numbers, the formula in column F
would be:
=SUMIF($B$2:$B2,$B2,$C$2:$C2)-SUMIF($B$2:$B2,$B2,$D$2:$D2)