sumproduct return value by comparing two criteria.....

[batman07]

I have searched and searched these groups and need to ask for
assistance. Here is a sample of my data:

Sheet 1
Col A Col B
2007011 A
2007012 B
2007013 C
2007014 D
2007021 E

Sheet 2 Col B Col E
2007011 A 300
2007012 B 310
2007013 C 320
2007014 D 310
2007021 E 320

Using the value's in Col A & B in Sheet 1, I need to return the value
in Col E in Sheet 2. I know that a vlookup will not work. Have been
experimenting w/different variations of SUMPRODUCT, but cannot get it
to work.

Any and all help will be greatly appreciated!!!

Thanks,
Jason

 

[Dave Peterson]

=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!b1:b100=b2),(e1:e100))

Will add up all the values in column E that match both column A and B.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
You could use a different formula if you wanted to return just the first match
-- nice if you're retrieving text.

 

[batman07]

Thanks Dave, I think you have me going in the right direction. I need
the formula to work as more of a vlookup. The reason I can't use
vlookup is because I need to match two cells in Sheet1 to two cells in
Sheet2, then return the value a couple of columns over from Sheet2 into
Sheet1.

The two cells (in separate columns) in both sheets are text and
numeric. Ex. Col A is numeric, Col B is text. Col E in Sheet 2 is
numeric.

Sheet1
Col B is the date.
Col C is the type of work.
Col D = value from Col E, Sheet2

Sheet2
Col A is the date.
Col B is the type of work.
Col E is one measurement of the type of work.

Sheet1 is an overview of the data on Sheet2. Sheet2, Col E is a
particular metric we're focusing on - which we need to pull into sheet
one Col D.

Dave, your help is appreciated!

Thanks,
Jason

 

[Dave Peterson]

Remember that =sumproduct() will return the sum of the matching records and if
there is only one matching record, then you're fine.

But if you want to return the first value (and that could be non-numeric)...

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[batman07]

Dave, your quick reply's are great! Thanks!!

I don't think the Index and Match formula below will work. I thank you
for your patience and if you have more, I'll try to explain what I'm
doing.

In the 'Overview' tab, Column B has the date. Column C has the type of
work. Column D needs the measurement of that work.

Col B Col C Col D
Date Work AHT
Ex. 2006011 Service 310


In the 'GSO' tab, Column A has the date. Column B has the type of
work. Column E has the measurement of that work. (This sheet contains
alot of data - Column E is what we need).

Col B Col C Col E
Date Work AHT
Ex. 2006011 Service 310


Column B in both sheets contains different dates. Column C contains 5
different types of work. Column E is always numeric and changes daily.


I cannot use vlookup because I need to match the date and work in the
'Overview' tab with the date and work in the 'GSO' tab. If there was
only one field in each sheet to match, vlookup would be perfect. I
thought this could be done with sumproduct. But I do not need to sum,
count, mulitply, etc., anything. Just return the value from 'GSO' to
'Overview'.

Dave, thanks again!

Jason

 

[Dave Peterson]

What was the formula you tried that didn't work?

Dave, your quick reply's are great! Thanks!!

I don't think the Index and Match formula below will work. I thank you
for your patience and if you have more, I'll try to explain what I'm
doing.

In the 'Overview' tab, Column B has the date. Column C has the type of
work. Column D needs the measurement of that work.

Col B Col C Col D
Date Work AHT
Ex. 2006011 Service 310

In the 'GSO' tab, Column A has the date. Column B has the type of
work. Column E has the measurement of that work. (This sheet contains
alot of data - Column E is what we need).

Col B Col C Col E
Date Work AHT
Ex. 2006011 Service 310

Column B in both sheets contains different dates. Column C contains 5
different types of work. Column E is always numeric and changes daily.

I cannot use vlookup because I need to match the date and work in the
'Overview' tab with the date and work in the 'GSO' tab. If there was
only one field in each sheet to match, vlookup would be perfect. I
thought this could be done with sumproduct. But I do not need to sum,
count, mulitply, etc., anything. Just return the value from 'GSO' to
'Overview'.

Dave, thanks again!

Jason

 

[batman07]

Dave, you're tha man! I went back and tried my formula (with several
variations) to reply w/something valid and figured it out! Here's the
formula that does exactly what I need:

=SUMPRODUCT((GSO!$A$3:$A$500=Overview!$B$4)*(GSO!$B$3:$B$500=Overview!$C$4)*(GSO!$E$3:$E$500)

I'm good to go!

Maybe I just overcomplicated it yesterday. But nonetheless, I
sincerely appreciate your patience and help!

Thanks,
Jason

 

[Dave Peterson]

I like the version that separates the parms with commas and uses the double
negative signs.

But yours will work fine, too.

Dave, you're tha man! I went back and tried my formula (with several
variations) to reply w/something valid and figured it out! Here's the
formula that does exactly what I need:

=SUMPRODUCT((GSO!$A$3:$A$500=Overview!$B$4)*(GSO!$B$3:$B$500=Overview!$C$4)*(GSO!$E$3:$E$500)

I'm good to go!

Maybe I just overcomplicated it yesterday. But nonetheless, I
sincerely appreciate your patience and help!

Thanks,
Jason