VLOOKUP & Array
 

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

Try...

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A3,{"a","b","c"},0)),B1:B3)

Note, however, if "a", "b", or "c" occur more than once in the range
A1:A3, all corresponding values in Column B will be summed.

Hope this helps!

http://www.xl-central.com

In article ,
AlanR wrote:

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

Try this:

=SUMPRODUCT((A1:A3="a")+(A1:A3="b")+(A1:A3="c"),B1 :B3)

HTH
Elkar


"AlanR" wrote:

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

Here's another one:

=SUM(SUMIF(A1:A3,{"a","b","c"},B1:B3))

--
Biff
Microsoft Excel MVP


"AlanR" wrote in message
...
I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

Domenic wrote...
Try...

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A3,{"a","b","c"},0)),B1:B3)

Note, however, if "a", "b", or "c" occur more than once in the
range A1:A3, all corresponding values in Column B will be summed.
....

If duplicates could be an issue and the OP wants only the topmost
match for each,

=SUMPRODUCT(ISNUMBER(1/(MATCH({"a","b","c"},A1:A100,0)
=ROW(A1:A100)-MIN(ROW(A1:A100))+1))*B1:B100)

VLOOKUP & Array
 

Sorry, I wasn't clear when I asked the question:

I have a table of names (Column A) and values (Column B).

I want to take a list of cells, (eg A1:A9) and translate them into values,
then add them up.

So in Sheet1 I have:

A B
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9

In Sheet2 I have:
A
a
a
a
a
b
c
d
g
i

So I want to total up the names by getting the values from Sheet1.

"AlanR" wrote:

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

Based on the sample you posted what result do you expect?

--
Biff
Microsoft Excel MVP


"AlanR" wrote in message
...
Sorry, I wasn't clear when I asked the question:

I have a table of names (Column A) and values (Column B).

I want to take a list of cells, (eg A1:A9) and translate them into values,
then add them up.

So in Sheet1 I have:

A B
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9

In Sheet2 I have:
A
a
a
a
a
b
c
d
g
i

So I want to total up the names by getting the values from Sheet1.

"AlanR" wrote:

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

So Sheet 2 would add up to:

A
a =1
a =1
a =1
a =1
b =2
c =3
d =4
g =7
i =9

So the total would be (1+1+1+1+2+3+4+7+9) = 29

"T. Valko" wrote:

Based on the sample you posted what result do you expect?

--
Biff
Microsoft Excel MVP


"AlanR" wrote in message
...
Sorry, I wasn't clear when I asked the question:

I have a table of names (Column A) and values (Column B).

I want to take a list of cells, (eg A1:A9) and translate them into values,
then add them up.

So in Sheet1 I have:

A B
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9

In Sheet2 I have:
A
a
a
a
a
b
c
d
g
i

So I want to total up the names by getting the values from Sheet1.

"AlanR" wrote:

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6

VLOOKUP & Array
 

Assuming you enter the formula on Sheet2:

=SUMPRODUCT(COUNTIF(A1:A9,Sheet1!A1:A9),Sheet1!B1: B9)

--
Biff
Microsoft Excel MVP


"AlanR" wrote in message
...
So Sheet 2 would add up to:

A
a =1
a =1
a =1
a =1
b =2
c =3
d =4
g =7
i =9

So the total would be (1+1+1+1+2+3+4+7+9) = 29

"T. Valko" wrote:

Based on the sample you posted what result do you expect?

--
Biff
Microsoft Excel MVP


"AlanR" wrote in message
...
Sorry, I wasn't clear when I asked the question:

I have a table of names (Column A) and values (Column B).

I want to take a list of cells, (eg A1:A9) and translate them into
values,
then add them up.

So in Sheet1 I have:

A B
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9

In Sheet2 I have:
A
a
a
a
a
b
c
d
g
i

So I want to total up the names by getting the values from Sheet1.

"AlanR" wrote:

I have a sheet that looks like:
A B
a 1
b 2
c 3

=vlookup("a",A1:B3,2,0)=1
=vlookup("b",A1:B3,2,0)=2
=vlookup("c",A1:B3,2,0)=3

I want to add values {"a","b","c"}, so

=sum(vlookup({"a","b","c"},A1:B3,2,0))=sum({1,2,3} )=6