Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
Why doesn't VLOOKUP work?
-- Brevity is the soul of wit. " wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
=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 he 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. wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
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 wrote: =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 he 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. wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
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 wrote: 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)) wrote: 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 wrote: =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 he 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. wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
What was the formula you tried that didn't work?
wrote: 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 wrote: 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)) wrote: 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 wrote: =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 he 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
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 wrote: What was the formula you tried that didn't work? wrote: 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 wrote: 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)) wrote: 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 wrote: =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 he 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct return value by comparing two criteria.....
I like the version that separates the parms with commas and uses the double
negative signs. But yours will work fine, too. wrote: 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 wrote: What was the formula you tried that didn't work? wrote: 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 wrote: 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)) wrote: 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 wrote: =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 he 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. wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
SumProduct with criteria list | Excel Worksheet Functions | |||
Sumproduct w/date criteria not working | Excel Worksheet Functions | |||
Unique sumproduct with criteria! | Excel Worksheet Functions | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions |