Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup returning the sum of multiple values from one "code"
Here is my array:
Col A Col B Row 1 M 1 Row 2 M 2 Row 3 M 3 Row 4 N 4 Row 5 N 5 Not sure if the title of my question is correct, but I think I need to use the VLookup function...I need to achieve the following for a dataset: Assuming that Column A has a set of some text or codes (in my example just two--"M" and "N"), and Column B has a set of corresponding numbers, I need to have a formula (in another worksheet), that totals all values from one code. for example, to total all rows where the row code is "M", the answer to the formula would be "6" (1+2+3), and all rows where the code "N" exists, that answer would yield the number/value of "9" (4+5). Hope this is clear, and thanks for any help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup returning the sum of multiple values from one "code"
Hi
You can use sumif function (assuming the data set is in Sheet 1): In Sheet 2: =sumif(Sheet1!A1:A5,"=M",Sheet1!B1:B5) and do same fo "N" hope this helps -- exalan "Matt" wrote: Here is my array: Col A Col B Row 1 M 1 Row 2 M 2 Row 3 M 3 Row 4 N 4 Row 5 N 5 Not sure if the title of my question is correct, but I think I need to use the VLookup function...I need to achieve the following for a dataset: Assuming that Column A has a set of some text or codes (in my example just two--"M" and "N"), and Column B has a set of corresponding numbers, I need to have a formula (in another worksheet), that totals all values from one code. for example, to total all rows where the row code is "M", the answer to the formula would be "6" (1+2+3), and all rows where the code "N" exists, that answer would yield the number/value of "9" (4+5). Hope this is clear, and thanks for any help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup returning the sum of multiple values from one "code"
Try it this way:
=SUMIF(Sheet1!A:A,"M",Sheet1!B:B) Or you could put the M into a cell (A1 on Sheet2), then it becomes: =SUMIF(Sheet1!A:A,A1,Sheet1!B:B) Hope this helps. Pete On Apr 28, 1:02*pm, Matt wrote: Here is my array: * * * * Col A * Col B Row 1 * M * * * 1 Row 2 * M * * * 2 Row 3 * M * * * 3 Row 4 * N * * * 4 Row 5 * N * * * 5 Not sure if the title of my question is correct, but I think I need to use the VLookup function...I need to achieve the following for a dataset: Assuming that Column A has a set of some text or codes (in my example just two--"M" and "N"), and Column B has a set of corresponding numbers, I need to have a formula (in another worksheet), that totals all values from one code. * for example, to total all rows where the row code is "M", the answer to the formula would be "6" (1+2+3), and all rows where the code "N" exists, that answer would yield the number/value of "9" (4+5). Hope this is clear, and thanks for any help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup returning the sum of multiple values from one "code"
Great, thank you both!
"Pete_UK" wrote: Try it this way: =SUMIF(Sheet1!A:A,"M",Sheet1!B:B) Or you could put the M into a cell (A1 on Sheet2), then it becomes: =SUMIF(Sheet1!A:A,A1,Sheet1!B:B) Hope this helps. Pete On Apr 28, 1:02 pm, Matt wrote: Here is my array: Col A Col B Row 1 M 1 Row 2 M 2 Row 3 M 3 Row 4 N 4 Row 5 N 5 Not sure if the title of my question is correct, but I think I need to use the VLookup function...I need to achieve the following for a dataset: Assuming that Column A has a set of some text or codes (in my example just two--"M" and "N"), and Column B has a set of corresponding numbers, I need to have a formula (in another worksheet), that totals all values from one code. for example, to total all rows where the row code is "M", the answer to the formula would be "6" (1+2+3), and all rows where the code "N" exists, that answer would yield the number/value of "9" (4+5). Hope this is clear, and thanks for any help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup returning the sum of multiple values from one "code"
You're welcome, matt - thanks for feeding back.
Pete On Apr 28, 1:46*pm, Matt wrote: Great, thank you both! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup if statement returning a "false" answer. | Excel Worksheet Functions | |||
isna vlookup returning"0" instead of " " | Excel Worksheet Functions | |||
problems with displaying "duplicate vlookup values" in same column | Excel Discussion (Misc queries) | |||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |