| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use vlookup in this case
Hello Guys,
On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
one way:
=SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
The problem exsists becouse all the time new codes are added to the list.
First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
SUMPRODUCT will do the calculation: question is how do decide which codes to
include i.e. which are codes for Europe? Perhaps a sample of your data might help. "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
If you want to use the formula on a another sheet, assume the data is on sheet1
=SUMPRODUCT((Sheet1!$A$1:$A$4={4598,6987})*(Sheet1 !$B$1:$B$4)) worked fine for me. If you add new code, make the A4 to be A1000 and B4 to be B1000 (as an example). this will allow room for growth. The formula worked for me. -- Regards, Tom Ogilvy "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
Data semple
codes values 0030 353.072,73 003 353.072,73 0080 0,00 00800 0,00 008 0,00 00 233.556,73 0100 1.112.130,26 010 1.112.130,26 0150 0,00 01500 0,00 0200 1.175.316,77 020 1.175.316,77 0210 8.981.103,98 0211 102.870,66 sales EU = code (003+0100+0210) -- mj "Toppers" wrote: SUMPRODUCT will do the calculation: question is how do decide which codes to include i.e. which are codes for Europe? Perhaps a sample of your data might help. "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
Further to Tom's reply:
If each code has a "region" identity e.g. EU for Europe (say in Column C) then you could use: =SUMPRODUCT((Sheet1!$C$1:$C$4="EU")*(Sheet1!$B$1:$ B$4)) As new codes for Europe are added/deleted, the formula would take account of these updates. Better still, put "EU" in a cell and use: =SUMPRODUCT((Sheet1!$C$1:$C$4=A2)*(Sheet1!$B$1:$B$ 4)) where A2 on your second sheet contains the text "EU" (no quotes). codes values Region 0030 353.072,73 003 353.072,73 EU 0080 0,00 00800 0,00 008 0,00 00 233.556,73 0100 1.112.130,26 EU 010 1.112.130,26 0150 0,00 01500 0,00 0200 1.175.316,77 020 1.175.316,77 0210 8.981.103,98 EU 0211 102.870,66 HTH "Tom Ogilvy" wrote: If you want to use the formula on a another sheet, assume the data is on sheet1 =SUMPRODUCT((Sheet1!$A$1:$A$4={4598,6987})*(Sheet1 !$B$1:$B$4)) worked fine for me. If you add new code, make the A4 to be A1000 and B4 to be B1000 (as an example). this will allow room for growth. The formula worked for me. -- Regards, Tom Ogilvy "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
As a result I get value 0. Where could be the problem.
-- mj "Toppers" wrote: Further to Tom's reply: If each code has a "region" identity e.g. EU for Europe (say in Column C) then you could use: =SUMPRODUCT((Sheet1!$C$1:$C$4="EU")*(Sheet1!$B$1:$ B$4)) As new codes for Europe are added/deleted, the formula would take account of these updates. Better still, put "EU" in a cell and use: =SUMPRODUCT((Sheet1!$C$1:$C$4=A2)*(Sheet1!$B$1:$B$ 4)) where A2 on your second sheet contains the text "EU" (no quotes). codes values Region 0030 353.072,73 003 353.072,73 EU 0080 0,00 00800 0,00 008 0,00 00 233.556,73 0100 1.112.130,26 EU 010 1.112.130,26 0150 0,00 01500 0,00 0200 1.175.316,77 020 1.175.316,77 0210 8.981.103,98 EU 0211 102.870,66 HTH "Tom Ogilvy" wrote: If you want to use the formula on a another sheet, assume the data is on sheet1 =SUMPRODUCT((Sheet1!$A$1:$A$4={4598,6987})*(Sheet1 !$B$1:$B$4)) worked fine for me. If you add new code, make the A4 to be A1000 and B4 to be B1000 (as an example). this will allow room for growth. The formula worked for me. -- Regards, Tom Ogilvy "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
Send sample w/book to:
toppers <at NOSPAMjohntopley.fsnet.co.uk remove NOSPAM and change <at to standard e-mail format "mj" wrote: As a result I get value 0. Where could be the problem. -- mj "Toppers" wrote: Further to Tom's reply: If each code has a "region" identity e.g. EU for Europe (say in Column C) then you could use: =SUMPRODUCT((Sheet1!$C$1:$C$4="EU")*(Sheet1!$B$1:$ B$4)) As new codes for Europe are added/deleted, the formula would take account of these updates. Better still, put "EU" in a cell and use: =SUMPRODUCT((Sheet1!$C$1:$C$4=A2)*(Sheet1!$B$1:$B$ 4)) where A2 on your second sheet contains the text "EU" (no quotes). codes values Region 0030 353.072,73 003 353.072,73 EU 0080 0,00 00800 0,00 008 0,00 00 233.556,73 0100 1.112.130,26 EU 010 1.112.130,26 0150 0,00 01500 0,00 0200 1.175.316,77 020 1.175.316,77 0210 8.981.103,98 EU 0211 102.870,66 HTH "Tom Ogilvy" wrote: If you want to use the formula on a another sheet, assume the data is on sheet1 =SUMPRODUCT((Sheet1!$A$1:$A$4={4598,6987})*(Sheet1 !$B$1:$B$4)) worked fine for me. If you add new code, make the A4 to be A1000 and B4 to be B1000 (as an example). this will allow room for growth. The formula worked for me. -- Regards, Tom Ogilvy "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to use vlookup in this case
Hi Toppers
I sended you w/book Regards, Mitja -- mj "Toppers" wrote: Send sample w/book to: toppers <at NOSPAMjohntopley.fsnet.co.uk remove NOSPAM and change <at to standard e-mail format "mj" wrote: As a result I get value 0. Where could be the problem. -- mj "Toppers" wrote: Further to Tom's reply: If each code has a "region" identity e.g. EU for Europe (say in Column C) then you could use: =SUMPRODUCT((Sheet1!$C$1:$C$4="EU")*(Sheet1!$B$1:$ B$4)) As new codes for Europe are added/deleted, the formula would take account of these updates. Better still, put "EU" in a cell and use: =SUMPRODUCT((Sheet1!$C$1:$C$4=A2)*(Sheet1!$B$1:$B$ 4)) where A2 on your second sheet contains the text "EU" (no quotes). codes values Region 0030 353.072,73 003 353.072,73 EU 0080 0,00 00800 0,00 008 0,00 00 233.556,73 0100 1.112.130,26 EU 010 1.112.130,26 0150 0,00 01500 0,00 0200 1.175.316,77 020 1.175.316,77 0210 8.981.103,98 EU 0211 102.870,66 HTH "Tom Ogilvy" wrote: If you want to use the formula on a another sheet, assume the data is on sheet1 =SUMPRODUCT((Sheet1!$A$1:$A$4={4598,6987})*(Sheet1 !$B$1:$B$4)) worked fine for me. If you add new code, make the A4 to be A1000 and B4 to be B1000 (as an example). this will allow room for growth. The formula worked for me. -- Regards, Tom Ogilvy "mj" wrote: The problem exsists becouse all the time new codes are added to the list. First we have to find the proper code in the list. EXempl. 4598 the value for this code is 698,00 EUR to the value of this code we have to add value of the code 6987 (the value is 987,00 EUR). The result we have to write to other sheet. -- mj "Toppers" wrote: one way: =SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4)) HTH "mj" wrote: Hello Guys, On one sheet a have a data base. Exempl. A (name - code) B (value) 1 4562 120,00 2 4598 698,00 3 5987 860,00 4 6987 987,00 On the other sheet I have to write some sum's from the previous sheet. total sales from region Europe = (4598 + 6987- code)=698,00+987,00 the problem is that the rows with codes are not on the same place all the time. They can be aded multiple rows with other codes. Becouse of that I was thinking to use VLOOKUP to find proper codes which they belong together and sum it. Can somebody give me a tip. Regards Mitja -- mj |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up vlookup so that it is case sensitive? | Excel Discussion (Misc queries) | |||
| How do I use case sensitive VLOOKUP? | Excel Worksheet Functions | |||
| vlookup -- Upper case and Lower case text | Excel Discussion (Misc queries) | |||
| VLookup & Case Sensitivity | Excel Worksheet Functions | |||
| Case sensitive vlookup | Excel Discussion (Misc queries) | |||
Linear Mode
