| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with a sum of array
Please some one!! help!!
I have been trying to solve this problem for couple of days, but not luck. Here is my question! I really hope someone can help me. I have a table with a job ID and working hours. Ex. Column "A" (jobs) 501350 500854 513501 254789 501350 Column "B" (working hours) 4 3 5 7 4 I have on another worksheet a list of all my current jobs without any time (work hours). I know how to use a vlookup formula to identify a job and return a specific value, but I need a combination of two formulas (maybe the vlookup and other) or a new formula to add the working hours of a specific job in my second worksheet. For example, if I have on column "A" of my second worksheet the job # 501350. I need a formula to look for this specific job# and add (sum) the working hours on all of them. In this example, it should be a value of "8". I need this value on column "B" next to the job on my second worksheet. Which is 4 + 4 on job# 501350 of my first worksheet. Thanks a bunch!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup with a sum of array
Try this:
Sheet2 A2 = 501350 =SUMIF(Sheet1!A$2:A$6,A$2,Sheet1!B$2:B$6) Biff "Jerry (the latin men)" <Jerry (the latin wrote in message ... Please some one!! help!! I have been trying to solve this problem for couple of days, but not luck. Here is my question! I really hope someone can help me. I have a table with a job ID and working hours. Ex. Column "A" (jobs) 501350 500854 513501 254789 501350 Column "B" (working hours) 4 3 5 7 4 I have on another worksheet a list of all my current jobs without any time (work hours). I know how to use a vlookup formula to identify a job and return a specific value, but I need a combination of two formulas (maybe the vlookup and other) or a new formula to add the working hours of a specific job in my second worksheet. For example, if I have on column "A" of my second worksheet the job # 501350. I need a formula to look for this specific job# and add (sum) the working hours on all of them. In this example, it should be a value of "8". I need this value on column "B" next to the job on my second worksheet. Which is 4 + 4 on job# 501350 of my first worksheet. Thanks a bunch!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup with a sum of array
Awesome!! this really work. Thanks a lot !!
"T. Valko" wrote: Try this: Sheet2 A2 = 501350 =SUMIF(Sheet1!A$2:A$6,A$2,Sheet1!B$2:B$6) Biff "Jerry (the latin men)" <Jerry (the latin wrote in message ... Please some one!! help!! I have been trying to solve this problem for couple of days, but not luck. Here is my question! I really hope someone can help me. I have a table with a job ID and working hours. Ex. Column "A" (jobs) 501350 500854 513501 254789 501350 Column "B" (working hours) 4 3 5 7 4 I have on another worksheet a list of all my current jobs without any time (work hours). I know how to use a vlookup formula to identify a job and return a specific value, but I need a combination of two formulas (maybe the vlookup and other) or a new formula to add the working hours of a specific job in my second worksheet. For example, if I have on column "A" of my second worksheet the job # 501350. I need a formula to look for this specific job# and add (sum) the working hours on all of them. In this example, it should be a value of "8". I need this value on column "B" next to the job on my second worksheet. Which is 4 + 4 on job# 501350 of my first worksheet. Thanks a bunch!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup with a sum of array
You're welcome. Thanks for the feedback!
Biff "Jerry (the latin men)" wrote in message ... Awesome!! this really work. Thanks a lot !! "T. Valko" wrote: Try this: Sheet2 A2 = 501350 =SUMIF(Sheet1!A$2:A$6,A$2,Sheet1!B$2:B$6) Biff "Jerry (the latin men)" <Jerry (the latin wrote in message ... Please some one!! help!! I have been trying to solve this problem for couple of days, but not luck. Here is my question! I really hope someone can help me. I have a table with a job ID and working hours. Ex. Column "A" (jobs) 501350 500854 513501 254789 501350 Column "B" (working hours) 4 3 5 7 4 I have on another worksheet a list of all my current jobs without any time (work hours). I know how to use a vlookup formula to identify a job and return a specific value, but I need a combination of two formulas (maybe the vlookup and other) or a new formula to add the working hours of a specific job in my second worksheet. For example, if I have on column "A" of my second worksheet the job # 501350. I need a formula to look for this specific job# and add (sum) the working hours on all of them. In this example, it should be a value of "8". I need this value on column "B" next to the job on my second worksheet. Which is 4 + 4 on job# 501350 of my first worksheet. Thanks a bunch!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup with a sum of array
=SUMPRODUCT(--(Sheet1!A2:A100=501350),Sheet1!B2:B100)
adjust to suit. (you can not use whole column ex. A:A or B:B) "Jerry (the latin men)" wrote: Please some one!! help!! I have been trying to solve this problem for couple of days, but not luck. Here is my question! I really hope someone can help me. I have a table with a job ID and working hours. Ex. Column "A" (jobs) 501350 500854 513501 254789 501350 Column "B" (working hours) 4 3 5 7 4 I have on another worksheet a list of all my current jobs without any time (work hours). I know how to use a vlookup formula to identify a job and return a specific value, but I need a combination of two formulas (maybe the vlookup and other) or a new formula to add the working hours of a specific job in my second worksheet. For example, if I have on column "A" of my second worksheet the job # 501350. I need a formula to look for this specific job# and add (sum) the working hours on all of them. In this example, it should be a value of "8". I need this value on column "B" next to the job on my second worksheet. Which is 4 + 4 on job# 501350 of my first worksheet. Thanks a bunch!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup with a sum of array
Awesome!!! I appreciate so much. Thanks!!
"Teethless mama" wrote: =SUMPRODUCT(--(Sheet1!A2:A100=501350),Sheet1!B2:B100) adjust to suit. (you can not use whole column ex. A:A or B:B) "Jerry (the latin men)" wrote: Please some one!! help!! I have been trying to solve this problem for couple of days, but not luck. Here is my question! I really hope someone can help me. I have a table with a job ID and working hours. Ex. Column "A" (jobs) 501350 500854 513501 254789 501350 Column "B" (working hours) 4 3 5 7 4 I have on another worksheet a list of all my current jobs without any time (work hours). I know how to use a vlookup formula to identify a job and return a specific value, but I need a combination of two formulas (maybe the vlookup and other) or a new formula to add the working hours of a specific job in my second worksheet. For example, if I have on column "A" of my second worksheet the job # 501350. I need a formula to look for this specific job# and add (sum) the working hours on all of them. In this example, it should be a value of "8". I need this value on column "B" next to the job on my second worksheet. Which is 4 + 4 on job# 501350 of my first worksheet. Thanks a bunch!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vLookup with multiple lookup value in table array | Excel Worksheet Functions | |||
| Array | Excel Worksheet Functions | |||
| How do i lengthen an existing VLOOKUP table array? | Excel Worksheet Functions | |||
| sumif based on vlookup array | Excel Worksheet Functions | |||
| How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) | |||
Hybrid Mode
