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!

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!

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!

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!

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!