# Vlookup question

#### DSLA

##### Active Member
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### patrickmuldoon99

##### Active Member
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
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
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
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

#### patrickmuldoon99

##### Active Member
Try:

=SUMIF(DATA1!D1:D500;A14;DATA1!K1:K500)

What happens now?

#### DSLA

##### Active Member
Now it gives the value 15

#### patrickmuldoon99

##### Active Member
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
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
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]

Replies
2
Views
250
Replies
3
Views
402
Replies
5
Views
194
Replies
0
Views
91
Replies
4
Views
132

1,171,463
Messages
5,875,656
Members
433,145
Latest member
nzltrippa

### 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.

### Which adblocker are you using?

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

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