| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Excel Data manipulation Pros: Something like a Vlookup with a Sum Function
Hi , Im trying to do a function that does a Sum of sheet 2 column B by the unique values in Column A, then returns that data to Sheet1 column B Sheet 1 A B 1160 ??? 1510 ??? 8150 ??? 8800 ??? Sheet 2 A B 1160 0.58 1160 0.58 1510 0.58 8150 0.58 8800 0.85 8800 0.85 8800 0.48 8800 0.58 The finished column should look like this Sheet 1 A B 1160 1.16 1510 .58 8150 .58 8800 2.76 I would be working with a list that has about 20000 numbers so manually summing them up would take pretty long. This might take multiple functions to get the data this way so for the excel pro's out there suggestions would be appreciated. -- vipjun ------------------------------------------------------------------------ vipjun's Profile: http://www.excelforum.com/member.php...o&userid=35266 View this thread: http://www.excelforum.com/showthread...hreadid=550396 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Excel Data manipulation Pros: Something like a Vlookup with a
VIP --
Let's say your source data is E1:F200, with the search category in Column E and the data to be summed in Column F. A B 1 1160 =SUMIF($E$1:$E$200,A1,$F$1:$F$200) 2 8800 copy down Should do the trick. HTH "vipjun" wrote: Hi , Im trying to do a function that does a Sum of sheet 2 column B by the unique values in Column A, then returns that data to Sheet1 column B Sheet 1 A B 1160 ??? 1510 ??? 8150 ??? 8800 ??? Sheet 2 A B 1160 0.58 1160 0.58 1510 0.58 8150 0.58 8800 0.85 8800 0.85 8800 0.48 8800 0.58 The finished column should look like this Sheet 1 A B 1160 1.16 1510 .58 8150 .58 8800 2.76 I would be working with a list that has about 20000 numbers so manually summing them up would take pretty long. This might take multiple functions to get the data this way so for the excel pro's out there suggestions would be appreciated. -- vipjun ------------------------------------------------------------------------ vipjun's Profile: http://www.excelforum.com/member.php...o&userid=35266 View this thread: http://www.excelforum.com/showthread...hreadid=550396 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Excel Data manipulation Pros: Something like a Vlookup with a
=SUMPRODUCT((Sheet2!$A$1:$A$8=Sheet1!A1)*(Sheet2!$ B$1:$B$8))
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Excel Data manipulation Pros: Something like a Vlookup with a Sum Function
vipjun wrote: Hi , Im trying to do a function that does a Sum of sheet 2 column B by the unique values in Column A, then returns that data to Sheet1 column B Sheet 1 A B 1160 ??? 1510 ??? 8150 ??? 8800 ??? Sheet 2 A B 1160 0.58 1160 0.58 1510 0.58 8150 0.58 8800 0.85 8800 0.85 8800 0.48 8800 0.58 The finished column should look like this Sheet 1 A B 1160 1.16 1510 .58 8150 .58 8800 2.76 =SUMIF(Sheet2!$A$2:$A$9,Sheet1!A2,Sheet2!$B$2:$B$9 ) Then drag that down the column, worked fine for me. Just make sure that the second arguement doesnt have any place holders (thats the $) and that all of the others do. Ghostwriter |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Excel Data manipulation Pros: Something like a Vlookup with a Sum Function
Thanks everyone, The formula works as stated. -- vipjun ------------------------------------------------------------------------ vipjun's Profile: http://www.excelforum.com/member.php...o&userid=35266 View this thread: http://www.excelforum.com/showthread...hreadid=550396 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| Use of Indirect function to fetch data from another Excel file | Excel Worksheet Functions | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| VLookup to merge Access Query data into Excel Report | Excel Discussion (Misc queries) | |||
| Can you use the validate function in a data form in Excel? | Excel Worksheet Functions | |||
Linear Mode
