Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
Can sumproduct be used to return cell content? If so, does anybody have a
sample that will requirte two conditions to be met. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
Can sumproduct be used to return cell content?
Numbers only, no text. -- Biff Microsoft Excel MVP "Richard" wrote in message ... Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
Give some details of your data, and what you are trying to do with it.
You can probably use an INDEX/MATCH combination, as SP returns numbers. Pete On Feb 18, 5:02*pm, Richard wrote: Can sumproduct be used to return cell content? *If so, does anybody have a sample that will requirte two conditions to be met. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
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 only use the whole column in xl2007. 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)) ============ If there is only one match and you're bringing back a number, you can use: =sumproduct(--(othersheet!a1:a10=ctree), --(othersheet!b1:b10=cleaf), (othersheet!c1:c10)) 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 Richard wrote: Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
Dave,
I appreciate the info, but this is not working for me. I get a #VALUE error. Any suggestions? Thanks, "Dave Peterson" wrote: 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 only use the whole column in xl2007. 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)) ============ If there is only one match and you're bringing back a number, you can use: =sumproduct(--(othersheet!a1:a10=ctree), --(othersheet!b1:b10=cleaf), (othersheet!c1:c10)) 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 Richard wrote: Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
Did you array enter it?
Did you use the full column? Are you sure you have matches in all your fields? It's worked for me lots of times. Richard wrote: Dave, I appreciate the info, but this is not working for me. I get a #VALUE error. Any suggestions? Thanks, "Dave Peterson" wrote: 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 only use the whole column in xl2007. 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)) ============ If there is only one match and you're bringing back a number, you can use: =sumproduct(--(othersheet!a1:a10=ctree), --(othersheet!b1:b10=cleaf), (othersheet!c1:c10)) 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 Richard wrote: Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
I did array enter it. Do I have to use the entire column?
"Dave Peterson" wrote: Did you array enter it? Did you use the full column? Are you sure you have matches in all your fields? It's worked for me lots of times. Richard wrote: Dave, I appreciate the info, but this is not working for me. I get a #VALUE error. Any suggestions? Thanks, "Dave Peterson" wrote: 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 only use the whole column in xl2007. 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)) ============ If there is only one match and you're bringing back a number, you can use: =sumproduct(--(othersheet!a1:a10=ctree), --(othersheet!b1:b10=cleaf), (othersheet!c1:c10)) 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 Richard wrote: Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
Nope--you can only use the full column in xl2007.
Check your ranges. Are they all the same size? How about the other questions? I think it's time to share the formula you tried. Richard wrote: I did array enter it. Do I have to use the entire column? "Dave Peterson" wrote: Did you array enter it? Did you use the full column? Are you sure you have matches in all your fields? It's worked for me lots of times. Richard wrote: Dave, I appreciate the info, but this is not working for me. I get a #VALUE error. Any suggestions? Thanks, "Dave Peterson" wrote: 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 only use the whole column in xl2007. 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)) ============ If there is only one match and you're bringing back a number, you can use: =sumproduct(--(othersheet!a1:a10=ctree), --(othersheet!b1:b10=cleaf), (othersheet!c1:c10)) 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 Richard wrote: Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Sumproduct return Text values
It works! I didn't use the entire column, that was the issue.
Thank you very much! "Dave Peterson" wrote: Nope--you can only use the full column in xl2007. Check your ranges. Are they all the same size? How about the other questions? I think it's time to share the formula you tried. Richard wrote: I did array enter it. Do I have to use the entire column? "Dave Peterson" wrote: Did you array enter it? Did you use the full column? Are you sure you have matches in all your fields? It's worked for me lots of times. Richard wrote: Dave, I appreciate the info, but this is not working for me. I get a #VALUE error. Any suggestions? Thanks, "Dave Peterson" wrote: 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 only use the whole column in xl2007. 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)) ============ If there is only one match and you're bringing back a number, you can use: =sumproduct(--(othersheet!a1:a10=ctree), --(othersheet!b1:b10=cleaf), (othersheet!c1:c10)) 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 Richard wrote: Can sumproduct be used to return cell content? If so, does anybody have a sample that will requirte two conditions to be met. Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
Return text using Sumproduct | Excel Worksheet Functions | |||
Quick Sumproduct vs Text return | Excel Discussion (Misc queries) | |||
Sumproduct to return a text | Excel Worksheet Functions | |||
Look up and return text values | Excel Worksheet Functions |