Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
In second column on Sheet1
=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100) This match code in (sheet1) A1 against codes on sheet2 and sum the values in column B (but sure if this what you want) If simply want count of number of times product occurs then: =Sumproduct(--(sheet2!$a$1:$a$100=a1)) Or =COUNTIF(Sheet2!$A$1:$A$4,"=" &A1) HTH "Lazclark" wrote: I have two spreadsheets - In one column on this 1st sheet, there is a product code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
Toppers - you live up to your name! Thanks for the speedy response...
Unfortunately my brain isn't working and i I still can't get it to work - here's an example of what i want Sheet 1 : A B C D Product Total Codes sales 1 apples 2 pears 3 bananas Sheet 2: s t u v Product Sales Codes 48 apples 20 49 pears 15 50 bananas 23 51 apples 8 52 apples 13 53 bananas 15 For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be 38... What formula do i need to type in sheet one column b1 for example? Many thanks! "Toppers" wrote: In second column on Sheet1 =Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100) This match code in (sheet1) A1 against codes on sheet2 and sum the values in column B (but sure if this what you want) If simply want count of number of times product occurs then: =Sumproduct(--(sheet2!$a$1:$a$100=a1)) Or =COUNTIF(Sheet2!$A$1:$A$4,"=" &A1) HTH "Lazclark" wrote: I have two spreadsheets - In one column on this 1st sheet, there is a product code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
Sorry - those sheets should have looked like :
Sheet 1 : ..........A................ B........... C .........D ......Product .........Total ......Codes ..........sales 1 ..apples 2 ..pears 3 ..bananas Sheet 2: ..............s...................t.............. u ...............v .............Product... Sales .............Codes 48 ......apples........ 20 49...... pears .........15 50..... bananas...... 23 51 ......apples .........8 52 .....apples .........13 53 ...bananas .........15 "Lazclark" wrote: Toppers - you live up to your name! Thanks for the speedy response... Unfortunately my brain isn't working and i I still can't get it to work - here's an example of what i want Sheet 1 : A B C D Product Total Codes sales 1 apples 2 pears 3 bananas Sheet 2: s t u v Product Sales Codes 48 apples 20 49 pears 15 50 bananas 23 51 apples 8 52 apples 13 53 bananas 15 For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be 38... What formula do i need to type in sheet one column b1 for example? Many thanks! "Toppers" wrote: In second column on Sheet1 =Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100) This match code in (sheet1) A1 against codes on sheet2 and sum the values in column B (but sure if this what you want) If simply want count of number of times product occurs then: =Sumproduct(--(sheet2!$a$1:$a$100=a1)) Or =COUNTIF(Sheet2!$A$1:$A$4,"=" &A1) HTH "Lazclark" wrote: I have two spreadsheets - In one column on this 1st sheet, there is a product code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
Hi Lazclark,
did you try sumif? on the 2nd column of sheet 1 include: =sumif(a2,sheet2!a2:a1000,sheet2!b2:b1000) hth regards from Brazil Marcelo "Lazclark" escreveu: Sorry - those sheets should have looked like : Sheet 1 : .........A................ B........... C .........D .....Product .........Total .....Codes ..........sales 1 ..apples 2 ..pears 3 ..bananas Sheet 2: .............s...................t.............. u ...............v ............Product... Sales ............Codes 48 ......apples........ 20 49...... pears .........15 50..... bananas...... 23 51 ......apples .........8 52 .....apples .........13 53 ...bananas .........15 "Lazclark" wrote: Toppers - you live up to your name! Thanks for the speedy response... Unfortunately my brain isn't working and i I still can't get it to work - here's an example of what i want Sheet 1 : A B C D Product Total Codes sales 1 apples 2 pears 3 bananas Sheet 2: s t u v Product Sales Codes 48 apples 20 49 pears 15 50 bananas 23 51 apples 8 52 apples 13 53 bananas 15 For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be 38... What formula do i need to type in sheet one column b1 for example? Many thanks! "Toppers" wrote: In second column on Sheet1 =Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100) This match code in (sheet1) A1 against codes on sheet2 and sum the values in column B (but sure if this what you want) If simply want count of number of times product occurs then: =Sumproduct(--(sheet2!$a$1:$a$100=a1)) Or =COUNTIF(Sheet2!$A$1:$A$4,"=" &A1) HTH "Lazclark" wrote: I have two spreadsheets - In one column on this 1st sheet, there is a product code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
In cell B2 on Sheet1 and copy down
=SUMPRODUCT(--(Sheet2!$T$2:$T$7=A2),(Sheet2!$U$2:$U$7)) Change ranges of T & U to suit (product codes in T, Sales in U?) HTH "Lazclark" wrote: Sorry - those sheets should have looked like : Sheet 1 : .........A................ B........... C .........D .....Product .........Total .....Codes ..........sales 1 ..apples 2 ..pears 3 ..bananas Sheet 2: .............s...................t.............. u ...............v ............Product... Sales ............Codes 48 ......apples........ 20 49...... pears .........15 50..... bananas...... 23 51 ......apples .........8 52 .....apples .........13 53 ...bananas .........15 "Lazclark" wrote: Toppers - you live up to your name! Thanks for the speedy response... Unfortunately my brain isn't working and i I still can't get it to work - here's an example of what i want Sheet 1 : A B C D Product Total Codes sales 1 apples 2 pears 3 bananas Sheet 2: s t u v Product Sales Codes 48 apples 20 49 pears 15 50 bananas 23 51 apples 8 52 apples 13 53 bananas 15 For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be 38... What formula do i need to type in sheet one column b1 for example? Many thanks! "Toppers" wrote: In second column on Sheet1 =Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100) This match code in (sheet1) A1 against codes on sheet2 and sum the values in column B (but sure if this what you want) If simply want count of number of times product occurs then: =Sumproduct(--(sheet2!$a$1:$a$100=a1)) Or =COUNTIF(Sheet2!$A$1:$A$4,"=" &A1) HTH "Lazclark" wrote: I have two spreadsheets - In one column on this 1st sheet, there is a product code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup totals?
... Should be:
=sumif(sheet2!a2:a1000,a2,sheet2!b2:b1000) "Marcelo" wrote: Hi Lazclark, did you try sumif? on the 2nd column of sheet 1 include: =sumif(a2,sheet2!a2:a1000,sheet2!b2:b1000) hth regards from Brazil Marcelo "Lazclark" escreveu: Sorry - those sheets should have looked like : Sheet 1 : .........A................ B........... C .........D .....Product .........Total .....Codes ..........sales 1 ..apples 2 ..pears 3 ..bananas Sheet 2: .............s...................t.............. u ...............v ............Product... Sales ............Codes 48 ......apples........ 20 49...... pears .........15 50..... bananas...... 23 51 ......apples .........8 52 .....apples .........13 53 ...bananas .........15 "Lazclark" wrote: Toppers - you live up to your name! Thanks for the speedy response... Unfortunately my brain isn't working and i I still can't get it to work - here's an example of what i want Sheet 1 : A B C D Product Total Codes sales 1 apples 2 pears 3 bananas Sheet 2: s t u v Product Sales Codes 48 apples 20 49 pears 15 50 bananas 23 51 apples 8 52 apples 13 53 bananas 15 For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be 38... What formula do i need to type in sheet one column b1 for example? Many thanks! "Toppers" wrote: In second column on Sheet1 =Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100) This match code in (sheet1) A1 against codes on sheet2 and sum the values in column B (but sure if this what you want) If simply want count of number of times product occurs then: =Sumproduct(--(sheet2!$a$1:$a$100=a1)) Or =COUNTIF(Sheet2!$A$1:$A$4,"=" &A1) HTH "Lazclark" wrote: I have two spreadsheets - In one column on this 1st sheet, there is a product code. On another spreadsheet, one of the columns uses the corresponding product code on more than one occasion in the column. I need the second column of the first sheet to calculate the totals corresponding to the product codes in the second sheet..... So, rather than just return one value, i need vlookup to total all of them where the product code matches - is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |