vlookup, multiple values, sum values into one cell??

I have a table, listed below, and i need to return the sum of the matching
cells from the lookup. i think its explained pretty well below. The vlookup
formula stops after it finds the first match. i need it to find all matches,
sum them, and return to the cell.

A B C D E
1 PO# AMT

2 1003 75

3 1003 33

4 1006 21

5 1003 19

6 1006 67

Need to return:
Cell A9 -- 1006 - 88
Cell A10 -- 1003 - 127

Currently using the vlookup formula i can only return 75 for PO 1003. this
is because it is the first value listed.

Any clues on how to return all values next to PO 1003 and sum them??

Many thanks.

You can enter your PO's in a column, and then in the next column enter a
SumIf() formula that will reference the cells containing the PO's, so that
they'll be side by side.

For example, with 1006 in A9,
And 1003 in A10, enter this formula in B9, and copy down:

=SUMIF($A$2:$A$6,A9,$B$2:$B$6)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Phillips L" <Phillips [email protected]> wrote in message
news:[email protected]...
I have a table, listed below, and i need to return the sum of the matching
cells from the lookup. i think its explained pretty well below. The
vlookup
formula stops after it finds the first match. i need it to find all matches,
sum them, and return to the cell.

A B C D E
1 PO# AMT

2 1003 75

3 1003 33

4 1006 21

5 1003 19

6 1006 67

Need to return:
Cell A9 -- 1006 - 88
Cell A10 -- 1003 - 127

Currently using the vlookup formula i can only return 75 for PO 1003. this
is because it is the first value listed.

Any clues on how to return all values next to PO 1003 and sum them??

Many thanks.

Instead of vlookup(1003,A:B,2,false), use sumif(a:a,1003,b:b). SUMIF does
exactly what you're asking.

"Phillips L" wrote:

> I have a table, listed below, and i need to return the sum of the matching
> cells from the lookup. i think its explained pretty well below. The vlookup
> formula stops after it finds the first match. i need it to find all matches,
> sum them, and return to the cell.
>
> A B C D E
> 1 PO# AMT
>
> 2 1003 75
>
> 3 1003 33
>
> 4 1006 21
>
> 5 1003 19
>
> 6 1006 67
>
> Need to return:
> Cell A9 -- 1006 - 88
> Cell A10 -- 1003 - 127
>
> Currently using the vlookup formula i can only return 75 for PO 1003. this
> is because it is the first value listed.
>
> Any clues on how to return all values next to PO 1003 and sum them??
>
> Many thanks.

cheers mate, you've saved me many hours of doing nothing at work!! it was
fun for a while but much better now its solved.

thanks again.



"bpeltzer" wrote:

> Instead of vlookup(1003,A:B,2,false), use sumif(a:a,1003,b:b). SUMIF does
> exactly what you're asking.
>
> "Phillips L" wrote:
>
> > I have a table, listed below, and i need to return the sum of the matching
> > cells from the lookup. i think its explained pretty well below. The vlookup
> > formula stops after it finds the first match. i need it to find all matches,
> > sum them, and return to the cell.
> >
> > A B C D E
> > 1 PO# AMT
> >
> > 2 1003 75
> >
> > 3 1003 33
> >
> > 4 1006 21
> >
> > 5 1003 19
> >
> > 6 1006 67
> >
> > Need to return:
> > Cell A9 -- 1006 - 88
> > Cell A10 -- 1003 - 127
> >
> > Currently using the vlookup formula i can only return 75 for PO 1003. this
> > is because it is the first value listed.
> >
> > Any clues on how to return all values next to PO 1003 and sum them??
> >
> > Many thanks.

Hi L,

why don't you simply use a dynamic crosstable ?
1. it will make what you need,
2. if your datas chages, it will adapt (almost) automatically
3. you don't need to define the values of the PO that you have in your list
: it gives them so...


"Phillips L" wrote:

> I have a table, listed below, and i need to return the sum of the matching
> cells from the lookup. i think its explained pretty well below. The vlookup
> formula stops after it finds the first match. i need it to find all matches,
> sum them, and return to the cell.
>
> A B C D E
> 1 PO# AMT
>
> 2 1003 75
>
> 3 1003 33
>
> 4 1006 21
>
> 5 1003 19
>
> 6 1006 67
>
> Need to return:
> Cell A9 -- 1006 - 88
> Cell A10 -- 1003 - 127
>
> Currently using the vlookup formula i can only return 75 for PO 1003. this
> is because it is the first value listed.
>
> Any clues on how to return all values next to PO 1003 and sum them??
>
> Many thanks.