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

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

Help Excel Data manipulation Pros: Something like a Vlookup with a
 

=SUMPRODUCT((Sheet2!$A$1:$A$8=Sheet1!A1)*(Sheet2!$ B$1:$B$8))

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

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