How can I calculate total spend on items that have different values
 

I want to use a formular to calculate the total spent on a item in a
list and the total made from selling an item in a list. The item
quanaties varey as do the prices.

quantity type price transactionType
1 A 587708.08 Sell
1 A 587708.08 Sell
1 A 587708.08 Buy
5 B 220000 Buy
2 C 1499999 Buy
1 C 1000000 Buy
87573 D 4.8 Sell
5695 E 99.16 Buy
5691 E 98.06 Sell

I wish to use a formular to complete the following table.

Total Spent Total Income
A
B
C
D
E

How can I calculate total spend on items that have different value
 

Assuming your tabulation table Total Spent for A is cell B13, and so forth...
Type the following in B13:
=SUMPRODUCT(--(Trans="Buy"),--(UnitType=$A13),(Qty)*(UnitPrice))
And the follinwg ni C13:
=SUMPRODUCT(--(Trans="Sell"),--(UnitType=$A13),(Qty)*(UnitPrice))

Trans is the range of Buy/Sell
UnitType is the range of A/B/C/D, etc.
Qty is the range of quantitites for each
UnitPrice is the buy or sell value per unit.


--
John C


" wrote:

I want to use a formular to calculate the total spent on a item in a
list and the total made from selling an item in a list. The item
quanaties varey as do the prices.

quantity type price transactionType
1 A 587708.08 Sell
1 A 587708.08 Sell
1 A 587708.08 Buy
5 B 220000 Buy
2 C 1499999 Buy
1 C 1000000 Buy
87573 D 4.8 Sell
5695 E 99.16 Buy
5691 E 98.06 Sell

I wish to use a formular to complete the following table.

Total Spent Total Income
A
B
C
D
E