ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sum of Vlookup values (https://www.excelbanter.com/excel-worksheet-functions/66837-sum-vlookup-values.html)

SirSimons

Sum of Vlookup values
 
Hi

I have a workbook with 2 sheets.

In sheet 1 I have 2 colums. Colum A containing names, colum B containing
numbers.

On sheet 2 I would like to make a function that gives me the sum of all
numbers in sheet 1 with the same name in colum A.

If a type =sum(vlookup(...........)
I only get the first number per name from sheet 1.

/Jan

BenjieLop

Sum of Vlookup values
 

SirSimons Wrote:
Hi

I have a workbook with 2 sheets.

In sheet 1 I have 2 colums. Colum A containing names, colum B
containing
numbers.

On sheet 2 I would like to make a function that gives me the sum of
all
numbers in sheet 1 with the same name in colum A.

If a type =sum(vlookup(...........)
I only get the first number per name from sheet 1.

/Jan


ASSUMING that:

1. the range of your data in Sheet 1 is A1:B100;
2. the names entered in Sheet 2 are all in Column A

enter this formula in Cell B1, Sheet2:

=SUMIF(SHEET1!$A$1:$A$100,A1,SHEET1!$B$1:$B$100)

and copy down until your range requirement in Sheet 2 is met.

Hope this is what you are looking for.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=503995


SirSimons

Sum of Vlookup values
 
Hi

I can use the Sumif commando, but I would also like to get the 95% fraktile
of the numbers...

"BenjieLop" skrev:


SirSimons Wrote:
Hi

I have a workbook with 2 sheets.

In sheet 1 I have 2 colums. Colum A containing names, colum B
containing
numbers.

On sheet 2 I would like to make a function that gives me the sum of
all
numbers in sheet 1 with the same name in colum A.

If a type =sum(vlookup(...........)
I only get the first number per name from sheet 1.

/Jan


ASSUMING that:

1. the range of your data in Sheet 1 is A1:B100;
2. the names entered in Sheet 2 are all in Column A

enter this formula in Cell B1, Sheet2:

=SUMIF(SHEET1!$A$1:$A$100,A1,SHEET1!$B$1:$B$100)

and copy down until your range requirement in Sheet 2 is met.

Hope this is what you are looking for.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=503995




All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com