Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate total items meeting criteria in range with multiple shee | Excel Worksheet Functions | |||
calculate total number of items that meet 2 over multiple sheets | Excel Worksheet Functions | |||
Automatic totalling of spend on repeated items | Excel Discussion (Misc queries) | |||
How do I estimate the year spend if spend is $26000 as of May? | Excel Worksheet Functions | |||
Program to calculate how much I can afford to spend? | Excel Discussion (Misc queries) |