Would anyone have an idea of how to sum values based on a lookup.

eg.

- List 1 -
Col A Col B
b 2
d 4
e 1
x 3
y 2


List 2
a
b
c
d
e

What I need to do is add selected List 1 Col B values where List 1 Col
A values appear in List 2. So the result above would be 7.

Is there some array formula so I don't have to do a lengthy nested
SUMIF for approx 30 values it needs to look for in List 2.

I know I could insert another column with a VLOOKUP to return
something and then SUMIF on that return value, but it's for an
existing large spreadsheet and I 'd rather do it in a single formula
if possible.

Many thanks

Peter

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,E1:E5,0))),B1:B5)

Where E1:E5 = list2

--
Biff
Microsoft Excel MVP


wrote in message
...
Would anyone have an idea of how to sum values based on a lookup.

eg.

- List 1 -
Col A Col B
b 2
d 4
e 1
x 3
y 2


List 2
a
b
c
d
e

What I need to do is add selected List 1 Col B values where List 1 Col
A values appear in List 2. So the result above would be 7.

Is there some array formula so I don't have to do a lengthy nested
SUMIF for approx 30 values it needs to look for in List 2.

I know I could insert another column with a VLOOKUP to return
something and then SUMIF on that return value, but it's for an
existing large spreadsheet and I 'd rather do it in a single formula
if possible.

Many thanks

Peter

On Dec 19, 1:28 pm, wrote:
Would anyone have an idea of how to sum values based on a lookup.

eg.

- List 1 -
Col A Col B
b 2
d 4
e 1
x 3
y 2

List 2
a
b
c
d
e

What I need to do is add selected List 1 Col B values where List 1 Col
A values appear in List 2. So the result above would be 7.

Is there some array formula so I don't have to do a lengthy nested
SUMIF for approx 30 values it needs to look for in List 2.

I know I could insert another column with a VLOOKUP to return
something and then SUMIF on that return value, but it's for an
existing large spreadsheet and I 'd rather do it in a single formula
if possible.

Many thanks

Peter

I'm not that good at array-formulas but it should work:

=SUM((A1=$D$1:$D$19)*$E$1:$E$19)

where A1 is your List2 lookupfield
D1:D19 are your List1 lookupfields
E1:E19 are your List1 Valuefields

enter it with ctrl+shift+enter, excel will then add {} by itself to
the formula

Then you can copy it down

hth

Carlo

On Dec 19, 3:42 pm, "T. Valko" wrote:
Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,E1:E5,0))),B1:B5)

Where E1:E5 = list2

--
Biff
Microsoft Excel MVP

wrote in message

...



Would anyone have an idea of how to sum values based on a lookup.

eg.

- List 1 -
Col A Col B
b 2
d 4
e 1
x 3
y 2

List 2
a
b
c
d
e

What I need to do is add selected List 1 Col B values where List 1 Col
A values appear in List 2. So the result above would be 7.

Is there some array formula so I don't have to do a lengthy nested
SUMIF for approx 30 values it needs to look for in List 2.

I know I could insert another column with a VLOOKUP to return
something and then SUMIF on that return value, but it's for an
existing large spreadsheet and I 'd rather do it in a single formula
if possible.

Many thanks

Peter- Hide quoted text -

- Show quoted text -

Brilliant. That does the trick. Thanks Biff

wrote in message
...
On Dec 19, 3:42 pm, "T. Valko" wrote:
Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,E1:E5,0))),B1:B5)

Where E1:E5 = list2

--
Biff
Microsoft Excel MVP

wrote in message

...



Would anyone have an idea of how to sum values based on a lookup.

eg.

- List 1 -
Col A Col B
b 2
d 4
e 1
x 3
y 2

List 2
a
b
c
d
e

What I need to do is add selected List 1 Col B values where List 1 Col
A values appear in List 2. So the result above would be 7.

Is there some array formula so I don't have to do a lengthy nested
SUMIF for approx 30 values it needs to look for in List 2.

I know I could insert another column with a VLOOKUP to return
something and then SUMIF on that return value, but it's for an
existing large spreadsheet and I 'd rather do it in a single formula
if possible.

Many thanks

Peter- Hide quoted text -

- Show quoted text -

Brilliant. That does the trick. Thanks Biff

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP