how to use vlookup in this case

[Guest]

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

 

[Guest]

one way:

=SUMPRODUCT(($A$1:$A$4={4598,6987})*($B$1:$B$4))

HTH

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

As a result I get value 0. Where could be the problem.

 

[Guest]

Send sample w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM and change <at> to standard e-mail format

 

[Guest]

Hi Toppers

I sended you w/book


Regards,

Mitja