# Suming with criteria in cronilogical order

1. ## Suming with criteria in cronilogical order

I'm trying to work this out and can't figure it out. I would like a formula that i can drag down the "Current Holdings and Gain/loss" Columns that will give the following results....

Date Name Type Quantity Price Current Holdings Gain/Loss
1/1/2001 Stock a Initial 1000 1 1000 0
1/2/2001 Stock a Sell 100 2 900 100
1/3/2001 Stock a Buy 100 2.5 1000
1/4/2001 Stock a Sell 200 2 800 50loss+ 100Gain
1/5/2001 Stock a Sell 800 2 600 800
1/1/2001 Stock b Initial 1000 1000
1/2/2001 Stock b Buy 200 1200
1/3/2001 Stock b Sell 150 1050
1/4/2001 Stock b Buy 150 1200

Thanks!

2. ## Re: Suming with criteria in cronilogical order

Hi exclusiveicon

I was going to say that you cannot get a formula to do this, but then I thought that was a very arrogant thought, so let me say that I do not know how to write such a formula.

I'm not sure whether you realise, but you are asking for a LIFO system (Last in First Out) and this is (for me) the incredibly complicated bit. I could write some VBA to handle it, but not a formula.

If you are prepared to accept an average system (whereby the selling price is compared to the average price of the stock), I have written some formulas that will do the job. (Note that it is important to have a blank row between each type of stock)

Let me know how you get on

Regards
Alastair

3. ## Re: Suming with criteria in cronilogical order

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

There are currently 1 users browsing this thread. (0 members and 1 guests)