Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting specific number in a table
Hello guys - we have prepared a big spreadsheet for Stock Shares. In one we
have all the transaction that we do. The other sheets, are just copies of the first one - one for each share. This way if we use filter on the names we just have in each sheet the transactions for each share - thus can add up the number of shares, get average value etc etc.... Now the question is : Can i somehow get the information i want using just the sheet with all transactions and not creating a new spreadsheet each time for a new share? I will explain what i want in more details if someone did not understand. A1 = Microsoft , B1 = 500, C1= $54.76, D1= Purchase A2 = Google, B2 = 100, C1 = $100.35, D1= Purchase A3 =Microsoft , B3, = 25, C3= $57,76, D1= Purchase A4= Microsoft, B3 = 75, C3= $50, D1= Sale (the actual sheet is a bit more complicated) Is there a way to have in another cell the Total Shares of all the Microsoft purchases? And thus if it can be done, i could get the average price and some other things i have in mind. Thank you very much in advance guys!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting specific number in a table
=sumproduct(--(a1:a100="Microsoft),(b1:b100))
HTH Jim "Haris" wrote: Hello guys - we have prepared a big spreadsheet for Stock Shares. In one we have all the transaction that we do. The other sheets, are just copies of the first one - one for each share. This way if we use filter on the names we just have in each sheet the transactions for each share - thus can add up the number of shares, get average value etc etc.... Now the question is : Can i somehow get the information i want using just the sheet with all transactions and not creating a new spreadsheet each time for a new share? I will explain what i want in more details if someone did not understand. A1 = Microsoft , B1 = 500, C1= $54.76, D1= Purchase A2 = Google, B2 = 100, C1 = $100.35, D1= Purchase A3 =Microsoft , B3, = 25, C3= $57,76, D1= Purchase A4= Microsoft, B3 = 75, C3= $50, D1= Sale (the actual sheet is a bit more complicated) Is there a way to have in another cell the Total Shares of all the Microsoft purchases? And thus if it can be done, i could get the average price and some other things i have in mind. Thank you very much in advance guys!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting specific number in a table
Tiny correction. I think Jim intended to say:
=sumproduct(--(a1:a100="Microsoft"),(b1:b100)) -- David Biddulph "Jim" wrote in message ... =sumproduct(--(a1:a100="Microsoft),(b1:b100)) "Haris" wrote: Hello guys - we have prepared a big spreadsheet for Stock Shares. In one we have all the transaction that we do. The other sheets, are just copies of the first one - one for each share. This way if we use filter on the names we just have in each sheet the transactions for each share - thus can add up the number of shares, get average value etc etc.... Now the question is : Can i somehow get the information i want using just the sheet with all transactions and not creating a new spreadsheet each time for a new share? I will explain what i want in more details if someone did not understand. A1 = Microsoft , B1 = 500, C1= $54.76, D1= Purchase A2 = Google, B2 = 100, C1 = $100.35, D1= Purchase A3 =Microsoft , B3, = 25, C3= $57,76, D1= Purchase A4= Microsoft, B3 = 75, C3= $50, D1= Sale (the actual sheet is a bit more complicated) Is there a way to have in another cell the Total Shares of all the Microsoft purchases? And thus if it can be done, i could get the average price and some other things i have in mind. Thank you very much in advance guys!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a specific number to more general values in a table. | Excel Worksheet Functions | |||
Linking a Specific Word to a Specific Number | Excel Worksheet Functions | |||
Linking a Specific Word to a Specific Number | Excel Worksheet Functions | |||
Linking a Specific Word to a Specific Number | Excel Worksheet Functions | |||
Search for a number in a table and return data of a specific cell | Excel Discussion (Misc queries) |