Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
Good morning.
I know there's a sumproduct solution, but I can't seem to nail it. I need to pull data out of a table based on two criteria, one of which may not always exist. Here's the source table, stored on worksheet 'SOURCE': A B C D 1 Code Mod Desc Value A .. .. .. 50 12345 Yada yada 6.5 51 12345 A Yada yada 2.2 52 12345 B Yada yada 4.3 This table has about 6000 rows. The description doesn't vary, but the 'Value A' does. (And there are values B, C, and D for which I'll want to copy the working formula.) On another worksheet 'TARGET', I want to enter the code in column A, mod in column B, and pull the 'Value A' into column C using some sort of lookup or sumproduct, I think: A B C 1 12345 6.5 2 12345 A 2.2 3 12345 B 4.3 Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
=SUMPRODUCT(--(Sheet1!$A$1:$A$6000=!A1),--(Sheet1!$B$1:$B$6000=!B1),Sheet5!$D$1:$D$6000)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pdberger" wrote in message ... Good morning. I know there's a sumproduct solution, but I can't seem to nail it. I need to pull data out of a table based on two criteria, one of which may not always exist. Here's the source table, stored on worksheet 'SOURCE': A B C D 1 Code Mod Desc Value A . . . 50 12345 Yada yada 6.5 51 12345 A Yada yada 2.2 52 12345 B Yada yada 4.3 This table has about 6000 rows. The description doesn't vary, but the 'Value A' does. (And there are values B, C, and D for which I'll want to copy the working formula.) On another worksheet 'TARGET', I want to enter the code in column A, mod in column B, and pull the 'Value A' into column C using some sort of lookup or sumproduct, I think: A B C 1 12345 6.5 2 12345 A 2.2 3 12345 B 4.3 Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
Rather than sumproduct, consider AutoFilter. If if turn on AutoFilter:
Data Filter AutoFilter you can set separate criteria on each of the columns. Only the matching columns will be displayed and can be copied/pasted elsewhere. -- Gary''s Student - gsnu200761 "pdberger" wrote: Good morning. I know there's a sumproduct solution, but I can't seem to nail it. I need to pull data out of a table based on two criteria, one of which may not always exist. Here's the source table, stored on worksheet 'SOURCE': A B C D 1 Code Mod Desc Value A . . . 50 12345 Yada yada 6.5 51 12345 A Yada yada 2.2 52 12345 B Yada yada 4.3 This table has about 6000 rows. The description doesn't vary, but the 'Value A' does. (And there are values B, C, and D for which I'll want to copy the working formula.) On another worksheet 'TARGET', I want to enter the code in column A, mod in column B, and pull the 'Value A' into column C using some sort of lookup or sumproduct, I think: A B C 1 12345 6.5 2 12345 A 2.2 3 12345 B 4.3 Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
Bob --
You are absolutely the man. I had to remove the '!' (perhaps because I'm on Excel 2003?) but it works great. Thank you. Peter "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$1:$A$6000=!A1),--(Sheet1!$B$1:$B$6000=!B1),Sheet5!$D$1:$D$6000) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pdberger" wrote in message ... Good morning. I know there's a sumproduct solution, but I can't seem to nail it. I need to pull data out of a table based on two criteria, one of which may not always exist. Here's the source table, stored on worksheet 'SOURCE': A B C D 1 Code Mod Desc Value A . . . 50 12345 Yada yada 6.5 51 12345 A Yada yada 2.2 52 12345 B Yada yada 4.3 This table has about 6000 rows. The description doesn't vary, but the 'Value A' does. (And there are values B, C, and D for which I'll want to copy the working formula.) On another worksheet 'TARGET', I want to enter the code in column A, mod in column B, and pull the 'Value A' into column C using some sort of lookup or sumproduct, I think: A B C 1 12345 6.5 2 12345 A 2.2 3 12345 B 4.3 Thanks in advance! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
No that was my error. When I tested it, Excel put my sheet name in and I
tried to remove it for clarity, but missed the !s. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pdberger" wrote in message ... Bob -- You are absolutely the man. I had to remove the '!' (perhaps because I'm on Excel 2003?) but it works great. Thank you. Peter "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$1:$A$6000=!A1),--(Sheet1!$B$1:$B$6000=!B1),Sheet5!$D$1:$D$6000) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pdberger" wrote in message ... Good morning. I know there's a sumproduct solution, but I can't seem to nail it. I need to pull data out of a table based on two criteria, one of which may not always exist. Here's the source table, stored on worksheet 'SOURCE': A B C D 1 Code Mod Desc Value A . . . 50 12345 Yada yada 6.5 51 12345 A Yada yada 2.2 52 12345 B Yada yada 4.3 This table has about 6000 rows. The description doesn't vary, but the 'Value A' does. (And there are values B, C, and D for which I'll want to copy the working formula.) On another worksheet 'TARGET', I want to enter the code in column A, mod in column B, and pull the 'Value A' into column C using some sort of lookup or sumproduct, I think: A B C 1 12345 6.5 2 12345 A 2.2 3 12345 B 4.3 Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct question | Excel Discussion (Misc queries) | |||
SumProduct Question | Excel Discussion (Misc queries) | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
SumProduct Question | Excel Worksheet Functions | |||
another sumproduct question | Excel Worksheet Functions |