Sumif using Vlookup to compare a separate list

[petrons]

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

 

[T. Valko]

Try this:

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

Where E1:E5 = list2

 

[carlo]

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
D119 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

 

[petrons]

Try this:

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

Where E1:E5 = list2

--
Biff
Microsoft Excel MVP












- Show quoted text -

Brilliant. That does the trick. Thanks Biff

 

[T. Valko]

Brilliant. That does the trick. Thanks Biff

You're welcome. Thanks for the feedback!