Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array table and vlookup | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
vlookup with a sum of array | Excel Worksheet Functions | |||
VLOOKUP ARRAY | Excel Discussion (Misc queries) | |||
Array Function with VLOOKUP | Excel Worksheet Functions |