| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the
columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
try:
=SUMPRODUCT(--($A$1:$A$6="a"),$B$1:$B$6) Just like =sum() will treat text as 0, =sumproduct() with the comma syntax will do the same. 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 Greg Snidow wrote: Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
This array-entered** formula appears to do what you want...
=SUM(($A$1:$A$6="a")*IF(ISNUMBER($B$1:$B$6),$B$1:$ B$6,0)) **Commit formula using Ctrl+Shift+Enter and not just Enter by itself -- Rick (MVP - Excel) "Greg Snidow" wrote in message ... Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
Try changing you formula to this
=SUMPRODUCT(--($A$1:$A$6="a"),$B$1:$B$6) The double operator -- forces an evaluation and replacing the * with a , forces each array to evaluate seperately -- If this helps, please remember to click yes. "Greg Snidow" wrote: Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
Please note, I am trying all of your suggestions, with Paul's and Dave's
being the same, so I am replying to myself simply to consolidate the thread. First off, this is the real formula from my spreadsheet, and the one giving me errors... =SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c" )*($E$23:$E$1604="Best View-Current (SFU)")*(F$23:F$1604)) The final array, F$23:F$1604, is the one in question, where some of the cells have text values. This is what I have tried so far... Added dashes, and replaced '*' with ',' =SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),(F$23:F$1604)) This got rid of the error, but now returns a 0, where the answer when I delete the text cells is around 250,000. Tried entering it as an array formula by using ctrl+shift+enter, still with the dashes and ',' instead of '*' {=SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer is again 0. Tried entering it as an array formula without the dashes and with '*''s instead of ',''s... {=SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c ")*($E$23:$E$1604="Best View-Current (SFU)")*IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer was correct, so, Rick, thanks for the tip. Paul and Dave, I'm not sure exactly what the dashes are doing, nor why it did not work in my case. Do they have a technical name I could use to google more about it? Thank you all, for taking time out of your day to consider my problem and reply so promptly. Greg "Greg Snidow" wrote: Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
Try:
=SUMPRODUCT(--($A$23:$A$1604="o"), --($B$23:$B$1604="c"), --($E$23:$E$1604="Best View-Current (SFU)"), (F$23:F$1604)) 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 ==================== A23:A1604="o" will result in an array of 1582 true/falses. The -- stuff changes the true's to 1's and the false's to 0. Put TRUE in A1 put -a1 in B1 put --a1 in C1 And you'll see why you want two of them. Greg Snidow wrote: Please note, I am trying all of your suggestions, with Paul's and Dave's being the same, so I am replying to myself simply to consolidate the thread. First off, this is the real formula from my spreadsheet, and the one giving me errors... =SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c" )*($E$23:$E$1604="Best View-Current (SFU)")*(F$23:F$1604)) The final array, F$23:F$1604, is the one in question, where some of the cells have text values. This is what I have tried so far... Added dashes, and replaced '*' with ',' =SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),(F$23:F$1604)) This got rid of the error, but now returns a 0, where the answer when I delete the text cells is around 250,000. Tried entering it as an array formula by using ctrl+shift+enter, still with the dashes and ',' instead of '*' {=SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer is again 0. Tried entering it as an array formula without the dashes and with '*''s instead of ',''s... {=SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c ")*($E$23:$E$1604="Best View-Current (SFU)")*IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer was correct, so, Rick, thanks for the tip. Paul and Dave, I'm not sure exactly what the dashes are doing, nor why it did not work in my case. Do they have a technical name I could use to google more about it? Thank you all, for taking time out of your day to consider my problem and reply so promptly. Greg "Greg Snidow" wrote: Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
Dave, that formula also works. I think I did not fully understand the use of
the dashes, so I only had them in front of the first array. I like this method better, since it eliminates the use of an array formula, which could easily be messed up by users not understanding that you can not just type in the brackets. Thanks for the links, they are very informative. "Dave Peterson" wrote: Try: =SUMPRODUCT(--($A$23:$A$1604="o"), --($B$23:$B$1604="c"), --($E$23:$E$1604="Best View-Current (SFU)"), (F$23:F$1604)) 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 ==================== A23:A1604="o" will result in an array of 1582 true/falses. The -- stuff changes the true's to 1's and the false's to 0. Put TRUE in A1 put -a1 in B1 put --a1 in C1 And you'll see why you want two of them. Greg Snidow wrote: Please note, I am trying all of your suggestions, with Paul's and Dave's being the same, so I am replying to myself simply to consolidate the thread. First off, this is the real formula from my spreadsheet, and the one giving me errors... =SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c" )*($E$23:$E$1604="Best View-Current (SFU)")*(F$23:F$1604)) The final array, F$23:F$1604, is the one in question, where some of the cells have text values. This is what I have tried so far... Added dashes, and replaced '*' with ',' =SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),(F$23:F$1604)) This got rid of the error, but now returns a 0, where the answer when I delete the text cells is around 250,000. Tried entering it as an array formula by using ctrl+shift+enter, still with the dashes and ',' instead of '*' {=SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer is again 0. Tried entering it as an array formula without the dashes and with '*''s instead of ',''s... {=SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c ")*($E$23:$E$1604="Best View-Current (SFU)")*IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer was correct, so, Rick, thanks for the tip. Paul and Dave, I'm not sure exactly what the dashes are doing, nor why it did not work in my case. Do they have a technical name I could use to google more about it? Thank you all, for taking time out of your day to consider my problem and reply so promptly. Greg "Greg Snidow" wrote: Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. -- Dave Peterson . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text in column causing SUMPRODUCT error
Bob and J.E. did very nice work explaining how this works--and when you should
use the * operand, too! Greg Snidow wrote: Dave, that formula also works. I think I did not fully understand the use of the dashes, so I only had them in front of the first array. I like this method better, since it eliminates the use of an array formula, which could easily be messed up by users not understanding that you can not just type in the brackets. Thanks for the links, they are very informative. "Dave Peterson" wrote: Try: =SUMPRODUCT(--($A$23:$A$1604="o"), --($B$23:$B$1604="c"), --($E$23:$E$1604="Best View-Current (SFU)"), (F$23:F$1604)) 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 ==================== A23:A1604="o" will result in an array of 1582 true/falses. The -- stuff changes the true's to 1's and the false's to 0. Put TRUE in A1 put -a1 in B1 put --a1 in C1 And you'll see why you want two of them. Greg Snidow wrote: Please note, I am trying all of your suggestions, with Paul's and Dave's being the same, so I am replying to myself simply to consolidate the thread. First off, this is the real formula from my spreadsheet, and the one giving me errors... =SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c" )*($E$23:$E$1604="Best View-Current (SFU)")*(F$23:F$1604)) The final array, F$23:F$1604, is the one in question, where some of the cells have text values. This is what I have tried so far... Added dashes, and replaced '*' with ',' =SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),(F$23:F$1604)) This got rid of the error, but now returns a 0, where the answer when I delete the text cells is around 250,000. Tried entering it as an array formula by using ctrl+shift+enter, still with the dashes and ',' instead of '*' {=SUMPRODUCT(--($A$23:$A$1604="o"),($B$23:$B$1604="c"),($E$23:$E$ 1604="Best View-Current (SFU)"),IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer is again 0. Tried entering it as an array formula without the dashes and with '*''s instead of ',''s... {=SUMPRODUCT(($A$23:$A$1604="o")*($B$23:$B$1604="c ")*($E$23:$E$1604="Best View-Current (SFU)")*IF(ISNUMBER(F$23:F$1604)=FALSE,0,F$23:F$16 04))} And the answer was correct, so, Rick, thanks for the tip. Paul and Dave, I'm not sure exactly what the dashes are doing, nor why it did not work in my case. Do they have a technical name I could use to google more about it? Thank you all, for taking time out of your day to consider my problem and reply so promptly. Greg "Greg Snidow" wrote: Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that particular cell, but it does not work in the SUMPRODUCT formula like this... =SUMPRODUCT(($A$1:$A$6="a")*(IF(ISNUMBER($B$1:$B$6 )=FALSE,0,$B$1:$B$6))*($B$1:$B$6)) Any ideas on how I can deal with the text cells in column "B"? Deleting them is not an option. Thank you. -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct text and numbers same column | Excel Discussion (Misc queries) | |||
| Sumproduct a column where 2 adj text columns contain same value | New Users to Excel | |||
| 2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
| Sumproduct With Dates & Text In Same Column | Excel Programming | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
Linear Mode
