|
Finding text using multiple criteria
Is it possible to use something like this formula, amended as required, to
have it display the text from the cell where the first two parts of the formula are true? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please show what the formula would need to be? Rob |
Finding text using multiple criteria
Try this array formula** :
=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet!A1:A37 0=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in message ... Is it possible to use something like this formula, amended as required, to have it display the text from the cell where the first two parts of the formula are true? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please show what the formula would need to be? Rob |
Finding text using multiple criteria
Thanks Biff. Worked great!!
I Just added an IfError to get...... =IFERROR(INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(Work Sheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0 )),) Rob "T. Valko" wrote in message ... Try this array formula** : =INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet!A1:A37 0=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in message ... Is it possible to use something like this formula, amended as required, to have it display the text from the cell where the first two parts of the formula are true? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please show what the formula would need to be? Rob |
Finding text using multiple criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "RobN" wrote in message ... Thanks Biff. Worked great!! I Just added an IfError to get...... =IFERROR(INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(Work Sheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0 )),) Rob "T. Valko" wrote in message ... Try this array formula** : =INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet!A1:A37 0=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in message ... Is it possible to use something like this formula, amended as required, to have it display the text from the cell where the first two parts of the formula are true? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please show what the formula would need to be? Rob |
Finding text using multiple criteria
Biff,
Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Technically, *both* formulas are formulas that work with (manipulate)
arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Biff, you've been a great help.
After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your
first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Hi Rick,
My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Rick's assessment is the same that I would come to, also.
Consider this example: ...........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ...........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ...........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Biff, thankyou so much for that reply. All very useful.
I've discovered some text in one cell within the range J1:J370 which I'd forgotten about, as it was only supposed to be a temporary note to myself. (As I thought that column only had numeric values I couldn't understand why the #VALUE error when I was also sure I'd array entered it.....it had the curly brackets.) Thanks again! Rob "T. Valko" wrote in message ... Rick's assessment is the same that I would come to, also. Consider this example: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ..........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Just one more question, if I may.
Is it generally accepted that array formulas calculate faster? By that I mean comparing the array formula: =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) with this formula: =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) When I replaced over 18,000 cells containing the normal formula with the Array version, any subsequent calculations took about 8 seconds compared with about 12 secs. Rob "RobN" wrote in message ... Biff, thankyou so much for that reply. All very useful. I've discovered some text in one cell within the range J1:J370 which I'd forgotten about, as it was only supposed to be a temporary note to myself. (As I thought that column only had numeric values I couldn't understand why the #VALUE error when I was also sure I'd array entered it.....it had the curly brackets.) Thanks again! Rob "T. Valko" wrote in message ... Rick's assessment is the same that I would come to, also. Consider this example: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ..........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
You're welcome. Thanks for the feedback!
Also note: If there's text in column C you'll get a #VALUE! error when using the *array* formula: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) ...........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 But, if you use this syntax in a SUMPRODUCT formula it will ignore the text in column C: =SUMPRODUCT(--(A1:A5="x"),--(B1:B5="y"),C1:C5) -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, thankyou so much for that reply. All very useful. I've discovered some text in one cell within the range J1:J370 which I'd forgotten about, as it was only supposed to be a temporary note to myself. (As I thought that column only had numeric values I couldn't understand why the #VALUE error when I was also sure I'd array entered it.....it had the curly brackets.) Thanks again! Rob "T. Valko" wrote in message ... Rick's assessment is the same that I would come to, also. Consider this example: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ..........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Is it generally accepted that array formulas calculate faster?
No, it's just the opposite *although* some array formulas are faster to calulate versus non-array formulas as you've discovered. It really depends on what the formulas are doing, but *in general* an array formula is usually slower to calculate. -- Biff Microsoft Excel MVP "RobN" wrote in message ... Just one more question, if I may. Is it generally accepted that array formulas calculate faster? By that I mean comparing the array formula: =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) with this formula: =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) When I replaced over 18,000 cells containing the normal formula with the Array version, any subsequent calculations took about 8 seconds compared with about 12 secs. Rob "RobN" wrote in message ... Biff, thankyou so much for that reply. All very useful. I've discovered some text in one cell within the range J1:J370 which I'd forgotten about, as it was only supposed to be a temporary note to myself. (As I thought that column only had numeric values I couldn't understand why the #VALUE error when I was also sure I'd array entered it.....it had the curly brackets.) Thanks again! Rob "T. Valko" wrote in message ... Rick's assessment is the same that I would come to, also. Consider this example: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ..........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
Well that's very interesting! I think I'll stay away from the Array formulas
when I can, not only because of the usually slower calculation, but the problem, in my case, that they have trouble with text. Thanks Biff. Rob "T. Valko" wrote in message ... Is it generally accepted that array formulas calculate faster? No, it's just the opposite *although* some array formulas are faster to calulate versus non-array formulas as you've discovered. It really depends on what the formulas are doing, but *in general* an array formula is usually slower to calculate. -- Biff Microsoft Excel MVP "RobN" wrote in message ... Just one more question, if I may. Is it generally accepted that array formulas calculate faster? By that I mean comparing the array formula: =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) with this formula: =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) When I replaced over 18,000 cells containing the normal formula with the Array version, any subsequent calculations took about 8 seconds compared with about 12 secs. Rob "RobN" wrote in message ... Biff, thankyou so much for that reply. All very useful. I've discovered some text in one cell within the range J1:J370 which I'd forgotten about, as it was only supposed to be a temporary note to myself. (As I thought that column only had numeric values I couldn't understand why the #VALUE error when I was also sure I'd array entered it.....it had the curly brackets.) Thanks again! Rob "T. Valko" wrote in message ... Rick's assessment is the same that I would come to, also. Consider this example: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ..........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
Finding text using multiple criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "RobN" wrote in message ... Well that's very interesting! I think I'll stay away from the Array formulas when I can, not only because of the usually slower calculation, but the problem, in my case, that they have trouble with text. Thanks Biff. Rob "T. Valko" wrote in message ... Is it generally accepted that array formulas calculate faster? No, it's just the opposite *although* some array formulas are faster to calulate versus non-array formulas as you've discovered. It really depends on what the formulas are doing, but *in general* an array formula is usually slower to calculate. -- Biff Microsoft Excel MVP "RobN" wrote in message ... Just one more question, if I may. Is it generally accepted that array formulas calculate faster? By that I mean comparing the array formula: =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) with this formula: =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) When I replaced over 18,000 cells containing the normal formula with the Array version, any subsequent calculations took about 8 seconds compared with about 12 secs. Rob "RobN" wrote in message ... Biff, thankyou so much for that reply. All very useful. I've discovered some text in one cell within the range J1:J370 which I'd forgotten about, as it was only supposed to be a temporary note to myself. (As I thought that column only had numeric values I couldn't understand why the #VALUE error when I was also sure I'd array entered it.....it had the curly brackets.) Thanks again! Rob "T. Valko" wrote in message ... Rick's assessment is the same that I would come to, also. Consider this example: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 Array entered: =SUM((A1:A5="x")*(B1:B5="y")*C1:C5) The correct result is 2. T = TRUE, F = FALSE T*T*0 = 0 T*T*1 = 1 F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;1;0;0;1}) = 2 If you forget to array enter the formula you'll get a #VALUE! error. If the arrays aren't the same size you'll get a #N/A error: =SUM((A1:A10="x")*(B1:B5="y")*C1:C5) If you use entire columns as range references in versions of Excel prior to Excel 2007 you'll get a #NUM! error: =SUM((A:A="x")*(B:B="y")*C:C) If there's text in column C you'll get a #VALUE! error: ..........A..........B..........C 1........x...........y...........0 2........x...........y...........X 3....................y............1 4........x........................1 5........x..........y............1 T*T*0 = 0 T*T*X = #VALUE! F*T*1 = 0 T*F*1 = 0 T*T*1 = 1 =SUM({0;#VALUE!;0;0;1}) = #VALUE! If there's an error in *any* range you'll get that error. ..........A..........B..........C 1....#N/A........y...........0 2........x...........y...........1 3....................y............1 4........x........................1 5........x..........y............1 =SUM({#N/A;1;0;0;1}) = #N/A -- Biff Microsoft Excel MVP "RobN" wrote in message ... Hi Rick, My original post was regarding text, but my last post has to do with numeric. The text issue was answered by Biff, but I'm trying to understand arrays which led to this question. Rob "Rick Rothstein (MVP - VB)" wrote in message ... What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your first posting. If it is text, you can't extract it the way you are trying to by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical function (SUM... PRODUCT), each of its parts must ultimately be a numerical value of some sort. Rick "RobN" wrote in message ... Biff, you've been a great help. After reading through part of the cpearson site, to try and understand this, I modified my formula...... =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370) to an array formula........ =SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370) BUT I get a #Value error. I suspect it has something to do with the last part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the other sections have a TRUE at the correct position. Can you see why that would be? Rob "T. Valko" wrote in message ... Technically, *both* formulas are formulas that work with (manipulate) arrays. The difference is that the INDEX formula needs to be array entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not. The programmer that developed the SUMPRODUCT function wrote this array processing functionality directly into the function code. The INDEX formula could also be written in such a way that it does not need to be array entered: =INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0)) See if this helps: http://www.cpearson.com/Excel/ArrayFormulas.aspx -- Biff Microsoft Excel MVP "RobN" wrote in message ... Biff, Are you able to explain why this formula is an array formula =INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0)) AND why this one is not? =SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob |
| All times are GMT +1. The time now is 02:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com