Buy and sell formula problems

Use sumif;


B1:B8 is buy/sell
C1:C8 is the numbers

not exactly..i want to differentiate between the each buy/sell pairing of item a and item b seperately

any ideas?

I think in order to answer your question, we might need a bit more detail as to exactly what out of "Item A..buy$" are in which cells. For instance, Item ID (name, part number, ticker) in column A, Buy/Sell in column B, Amounts in column C ...

Ok so what I want to do is sum the prices sort of like a FIFO method. For instance, I want to keep the first Item A buy & sell distinct from the 2nd, 3rd, (and so on) incident of buy & sell.




Col I.....Col II.................Col III
date1....Item A desc.......buy price (-5$)
date2....Item B desc.......buy price (-3$)
date3....Item A desc......buy price (-9$)
date4....Item A desc.......sell price ($10)
date5....Item B desc.......sell price ($15)

So in this case, I want to show the first occurance of item A was $5 (10-5)
And the Item B transaction was $12 (15-3)

What about this format? Start the day with the only the Date and Seq columns filled in, with the Seq column being sequential 1 to n.

As buys occur, add them in the first row where Item is open. Set a sort for all rows in your table as Date, Item, Seq and sort the list every time you Buy a new lot.

The Seq column will keep your Buys in FIFO order, while the main list is in Item order.

For Sells, find the first open Sell cell for the item and enter that price. The Total column subtracts Buy from Sell giving your answer.

I hope this isn't too simple a reading of the problem.

Not sure if you've gotten your solution, but I'm still trying to write a FIFO/LIFO formula -- it has become a personal challenge, now. will try to post back when i have the time.

I'm playing around with Edell's solution, but if you can get a FIFO/LIFO sheet, I would forever be grateful.

I had a single-cell formula in mind, but I thnk it would fail if there were ever 2 buys/sells of the same item on the same day; this example, with the extra column added (can be hidden, put on another page), seems to work.

Formulas are--
in D2, dragged down: =IF(AND($C2<0,(SUMPRODUCT(($B$2:$B$20=B2)*($C$2:$C$20<0))<=SUMPRODUCT(($B$2:$B$20=$B2)*($C$2:$C$20>0)))),"Item Sold",IF($C2<0,"Item in Stock",IF(AND($C2>0,SUMPRODUCT(($B$2:$B$20=B2)*($C$2:$C$20<0))<SUMPRODUCT(($B$2:$B$20=$B2)*($C$2:$C$20>0))),"Error: Not in Stock!",IF(LEN($C2),$C2+SUMPRODUCT(($B$2:$B$20=$B2)*($C$2:$C$20<0)*($F$2:$F$20=$F2),($C$2:$C$20)),""))))

in F2, dragged down: =IF($C2<0,SUMPRODUCT(($B$2:$B2=$B2)*($C$2:$C2<0)),IF(LEN($C2),SUMPRODUCT(($B$2:$B2=$B2)*($C$2:$C2>0)),""))

Gawd, that looks hideous!

If you are amenable to reorganizing your data to:
you would get two benefits: finding the first unsold unit of an item will be easy; it will completely eliminate the risk of mismatches because of typos.