V-lookup and summing values if more than 1 matches criteria

I am trying to investigate a way in which I can get V-Lookup to sum the
values and bring back the total figure when more than one cell matches the
search criteria. E.g. if I am getting it to search for number 46 and I want
it to bring back the next column, and there are two 46 matches, and each of
the columns I want to bring back having a number in there, I want it to sum
the two numbers and bring back the total figure.

one way looking for 46 in col E and numbers in columns H:I
=SUM(INDIRECT("H"&MATCH(46,E:E)&":I"&MATCH(46,E:E)))

--
Don Guillett
SalesAid Software
[email protected]
"holcay" <[email protected]> wrote in message
news:[email protected]...
>I am trying to investigate a way in which I can get V-Lookup to sum the
> values and bring back the total figure when more than one cell matches the
> search criteria. E.g. if I am getting it to search for number 46 and I
> want
> it to bring back the next column, and there are two 46 matches, and each
> of
> the columns I want to bring back having a number in there, I want it to
> sum
> the two numbers and bring back the total figure.

Try the SUMIF function rather than vlookup; the format is roughly:
=sumif(Range to search, What to search for, Range to add upon matching). In
your case something like =sumif(B:B,46,C:C).

"holcay" wrote:

> I am trying to investigate a way in which I can get V-Lookup to sum the
> values and bring back the total figure when more than one cell matches the
> search criteria. E.g. if I am getting it to search for number 46 and I want
> it to bring back the next column, and there are two 46 matches, and each of
> the columns I want to bring back having a number in there, I want it to sum
> the two numbers and bring back the total figure.

Take a look at =sumif() in excel's help:

=sumif(sheet2!A:A,A1,sheet2!B:B)



holcay wrote:
>
> I am trying to investigate a way in which I can get V-Lookup to sum the
> values and bring back the total figure when more than one cell matches the
> search criteria. E.g. if I am getting it to search for number 46 and I want
> it to bring back the next column, and there are two 46 matches, and each of
> the columns I want to bring back having a number in there, I want it to sum
> the two numbers and bring back the total figure.

--

Dave Peterson