Vlookup question

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
I have a Vlookup on my excel sheet:
Vlookup(A14;DATA1!D1:O500;8;false)

It collect data from another excel sheet (DATA1), with a certain cell information (Cell A14).

Only problem is that there is more than one of the same information on the data sheet.

Here is what i would like to get done:
- I would like to have the Vlookup add (count) the information from the DATA1 sheet.

For example there is two of the same cell information, and the values are 1 and 30, i want the result to be 31. Because now the situation is that it only collect the information from either the 1 or the 30, bot both.

Please ask if there was something missing on the question, my english isn't so good :)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Sounds like you need a sumif

=SUMIF('DATA1'!D1:D500,A14,'DATA1'!O1:O500)

Try that, and see if its what you're after

HTH
 

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
this doesn't count the numbers together, i get a 0 for the result.

Vlookup(A14;DATA1!D1:O500;8;false)

It should search for the value (A14) in the sheet (DATA1) area (D1:O500), and then collect data from the same cell on the same row (8).

I only wan't it to count the numbers(from Row,cell,8) together if there is more than one of the same in the sheet (DATA1)

[/quote]
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Thats precisely what a sumif does

I'd advise you to construct the formula yourself using Excels formula builder... its not a complicated one.

From the help file:


SUMIF

Adds the cells specified by a given criteria.

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

Example

Suppose A1:A4 contain the following property values for four homes: $100,000, $200,000, $300,000, $400,000, respectively. B1:B4 contain the following sales commissions on each of the corresponding property values: $7,000, $14,000, $21,000, $28,000.
SUMIF(A1:A4,">160000",B1:B4) equals $63,000
 

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296

ADVERTISEMENT

I've entered this: =SUMIF(DATA1!D1:D500;A14;DATA1!O1:O500)

But it comes up with value O

When i use this:
=VLOOKUP(A14;DATA1!D1:O500;8;FALSE)

This comes up with value: 30
 

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296

ADVERTISEMENT

Now it gives the value 15
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Basically, the formula is matching whatever is in cell A14 to whatever is in the range D1:D500 in Data1. It is then summing whatever adjacent values it finds in the range K1:K500 on Data1. The formula is adding up whatever it finds in K1:K500, so whatever you are searchinig for must contain the sum of 15 in that column. Are you sure column K is what you are after?
 

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
Yes, but there is many other numbers also between the cells K1:K500.
I only want the specific cell that are it the same row as the matching value on the cell A14
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Yes, that is what a sumif formula does!
Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.
If you like, you can send the file to my email address specified on my profile and i'll take a look, because this seems very strange that its not working.[/quote]
 

Forum statistics

Threads
1,136,990
Messages
5,678,985
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top