Sumproduct question

[pdberger]

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!

 

[Bob Phillips]

=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)

 

[Gary''s Student]

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.

 

[pdberger]

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]

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)