Need help creating a running subtotal

[exceluser]

I'm trying to create a formula that returns a running net subtotal
(Profit and Loss) based on selling 100% of each product in stock.

This is how the correct output should look.

Date Product Transaction Price Qty. Net
1/1/2010 Apples Bought -$1.00 10 -$10.00
1/2/2010 Apples Bought -$1.00 5 -$15.00
1/3/2010 Apples Sold $2.00 15 $15.00
1/4/2010 Oranges Bought -$1.00 10 -$10.00
1/5/2010 Apples Bought -$1.00 10 -$10.00
1/6/2010 Apples Sold $2.00 10 $10.00

When a transaction = Bought, it means cash was needed to buy inventory
from a distributor which is why it is a negative number.

When a transaction = Sold, it means cash was received from a customer
who bought the inventory which is why it is a positive number.

The running subtotal formula should reside in each row of the Net
column.

When a transacton is made that sells the last of a product's
inventory, the difference between the cost of buying the product and
the income from the sale of the product should be reflected in that
row's subtotal.

The problem is that the running subtotal formulas I've created keep
adding money from inventory that has already been sold.

For example, the third Apples transaction reflects selling 100% of the
Apples inventory.

However, the fourth Apples transaction would keep adding the $15.00
profit to the running subtotal.

Any ideas on how to get the running subtotal to start over when all
inventory has been sold ?

 

[Ms-Exl-Learner]

I assume that your Starts From Column-A and Ends on Column-E and your
First Row is having the Column Headers like the below:-

Column-A
Date

Column-B
Product Transaction

Column-C
Price

Column-D
Qty.

Column-E
Net

Row/Col A B C D E
Row1 Date Product Transaction Price Qty. Net
Row2 1-Jan-10 Apples Bought -1.00 10.00
Row3 1-Feb-10 Apples Bought -1.00 5.00
Row4 1-Mar-10 Apples Sold 2.00 15.00
Row5 1-Apr-10 Oranges Bought -1.00 10.00
Row6 1-May-10 Apples Bought -1.00 10.00
Row7 1-Jun-10 Apples Sold 2.00 10.00

I Hope that your data starts from 2nd Row.

Copy and paste the below formula in F2 cell

=IF($D2<0,--("-"&SUMPRODUCT(($B$2:$B2=$B2)*($C$2:$C2=$C2)*($D
$2:$D2<0)*($E$2:$E2))),SUMPRODUCT(($B$2:$B2=$B2)*($C$2:$C2=$C2)*($D
$2:$D2>0)*($E$2:$E2)))

Drag the F2 cell formula to the remaining cells of Column-F.

Hope it’s clear!

 

[exceluser]

I assume that your Starts From Column-A and Ends on Column-E and your
First Row is having the Column Headers like the below:-

Column-A
Date

Column-B
Product Transaction

Column-C
Price

Column-D
Qty.

Column-E
Net

Row/Col A       B       C       D       E
Row1    Date    Product Transaction     Price   Qty.    Net
Row2    1-Jan-10        Apples  Bought  -1.00   10.00
Row3    1-Feb-10        Apples  Bought  -1.00   5.00
Row4    1-Mar-10        Apples  Sold    2.00    15.00
Row5    1-Apr-10        Oranges Bought  -1.00   10.00
Row6    1-May-10        Apples  Bought  -1.00   10.00
Row7    1-Jun-10        Apples  Sold    2.00    10.00

I Hope that your data starts from 2nd Row.

Copy and paste the below formula in F2 cell

=IF($D2<0,--("-"&SUMPRODUCT(($B$2:$B2=$B2)*($C$2:$C2=$C2)*($D
$2:$D2<0)*($E$2:$E2))),SUMPRODUCT(($B$2:$B2=$B2)*($C$2:$C2=$C2)*($D
$2:$D2>0)*($E$2:$E2)))

Drag the F2 cell formula to the remaining cells of Column-F.

Hope it’s clear!

-----------------------
Ms-Exl-Learner
-----------------------













- Show quoted text -

Ms-Exl-Learner,

Got it working.

Rumba thanks ... again.



Exceluser