Vlookup question

[DSLA]

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

 

[patrickmuldoon99]

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]

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]

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]

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]

Try:

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

What happens now?

 

[DSLA]

Now it gives the value 15

 

[patrickmuldoon99]

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]

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]

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]