Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column if two other columns' values appear in other sh
1. One sheet (Sheet1) contains a valid list of task numbers in column A.
2. Another sheet (Sheet2) contains a valid list of people's names in column A. 3. Another sheet (Sheet3) contains: a. Task number in column B. b. Person's name in column D. c. Number of hours worked in column F. 4. I need to sum the hours worked (Sheet3, column F) if the task number (Sheet3 column B) appears in Sheet1, column A AND the person's name (Sheet3, column D) appears in Sheet2, column A. I need the sum to appear in column J in Sheet2. Thanks for your help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column if two other columns' values appear in other sh
Assuming:
Task#s listed within A2:A20 in Sheet1, Task#s listed within B2:B100 in Sheet3, Names listed within D2:D100 in Sheet3, Hours worked within F2:F100 in Sheet3 In Sheet2, Names are listed in A2 down Put in J2: =SUMPRODUCT(ISNUMBER(MATCH(Sheet3!$B$2:$B$100,Shee t1!$A$2:$A$20,0))*(Sheet3! $D$2:$D$100=A2),(Sheet3!$F$2:$F$100)) Copy down Adapt the ranges to suit .. (note that we can't use entire col refs in SUMPRODUCT) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JulieU" wrote in message ... 1. One sheet (Sheet1) contains a valid list of task numbers in column A. 2. Another sheet (Sheet2) contains a valid list of people's names in column A. 3. Another sheet (Sheet3) contains: a. Task number in column B. b. Person's name in column D. c. Number of hours worked in column F. 4. I need to sum the hours worked (Sheet3, column F) if the task number (Sheet3 column B) appears in Sheet1, column A AND the person's name (Sheet3, column D) appears in Sheet2, column A. I need the sum to appear in column J in Sheet2. Thanks for your help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column if two other columns' values appear in othe
Max,
Thanks so much for your help! It worked perfectly! I had been struggling with this for days. Thanks again!! "Max" wrote: Assuming: Task#s listed within A2:A20 in Sheet1, Task#s listed within B2:B100 in Sheet3, Names listed within D2:D100 in Sheet3, Hours worked within F2:F100 in Sheet3 In Sheet2, Names are listed in A2 down Put in J2: =SUMPRODUCT(ISNUMBER(MATCH(Sheet3!$B$2:$B$100,Shee t1!$A$2:$A$20,0))*(Sheet3! $D$2:$D$100=A2),(Sheet3!$F$2:$F$100)) Copy down Adapt the ranges to suit .. (note that we can't use entire col refs in SUMPRODUCT) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JulieU" wrote in message ... 1. One sheet (Sheet1) contains a valid list of task numbers in column A. 2. Another sheet (Sheet2) contains a valid list of people's names in column A. 3. Another sheet (Sheet3) contains: a. Task number in column B. b. Person's name in column D. c. Number of hours worked in column F. 4. I need to sum the hours worked (Sheet3, column F) if the task number (Sheet3 column B) appears in Sheet1, column A AND the person's name (Sheet3, column D) appears in Sheet2, column A. I need the sum to appear in column J in Sheet2. Thanks for your help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column if two other columns' values appear in othe
Glad it worked, and thanks for the feedback !
Btw, although this doesn't affect the formula's return the parens around the last term: .. ,(Sheet3!$F$2:$F$100)) wasn't necessary. My error, just detected. It should read as just: .. ,Sheet3!$F$2:$F$100) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JulieU" wrote: Max, Thanks so much for your help! It worked perfectly! I had been struggling with this for days. Thanks again!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions |