Home 
Search 
Today's Posts 
#1




SUM(IF( Array to avoid #NUM! values
Hi again,
I posted a similar question a week or so back  but now I'm having trouble with using the SUMPRODUCT or SUM(IF( type function, especially when the ranges I'm referring to in these functions have #NUM! references. btw  thanks Frank, Aladin for your previous advice on a similar question. Using the SUMIF function somehow aviods this problem, but not when summing using multiple criterias (ie. SUMPRODUCT or SUM(IF( array type. Can anyone help me in avioding the #NUM!? Is it possible? Here is an example of the functions: =SUMPRODUCT((RangeName1="Customer")*(RangeName2=10 00)*TheSumRange) This returns #NUM!  I guess because all of my ranges include #NUM!. The other array type: =SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange) This also returns #NUM!. I tried wrapping this with an ISERROR but couldn't get it to work  eg. =SUM(IF(ISERROR(RangeName1="Customer")*(RangeName2 =1000),TheSumRange),,(RangeName1="Customer")*(Rang eName2=1000),TheSumRange)) Assuming I want to leave the error terms within the list  can this be done? Again your help appreciated. Elijah 
#2




Hi
post your used formulas for the range names One importan thing: Don't use ranges such as A:A. Use A1:A65000 instead (if you really need such huge ranges). If your ranges itself contain a #NUM error post the formulas which create these errors. You may change them to =IF(ISERROR(your_formula)),"",your_formula)  Regards Frank Kabel Frankfurt, Germany "Elijah" schrieb im Newsbeitrag om... Hi again, I posted a similar question a week or so back  but now I'm having trouble with using the SUMPRODUCT or SUM(IF( type function, especially when the ranges I'm referring to in these functions have #NUM! references. btw  thanks Frank, Aladin for your previous advice on a similar question. Using the SUMIF function somehow aviods this problem, but not when summing using multiple criterias (ie. SUMPRODUCT or SUM(IF( array type. Can anyone help me in avioding the #NUM!? Is it possible? Here is an example of the functions: =SUMPRODUCT((RangeName1="Customer")*(RangeName2=10 00)*TheSumRange) This returns #NUM!  I guess because all of my ranges include #NUM!. The other array type: =SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange) This also returns #NUM!. I tried wrapping this with an ISERROR but couldn't get it to work  eg. =SUM(IF(ISERROR(RangeName1="Customer")*(RangeName2 =1000),TheSumRange),, (RangeName1="Customer")*(RangeName2=1000),TheSumRa nge)) Assuming I want to leave the error terms within the list  can this be done? Again your help appreciated. Elijah 
#3




Hi!
Do you have #NUM! errors in *ALL* the ranges? If your errors are only in the sum_range: =SUM(IF(rng1="C",IF(rng2=10,IF(ISNUMBER (sum_rng),sum_rng)))) Array entered. You'd be better off correcting the error problem, though. Biff Original Message Hi again, I posted a similar question a week or so back  but now I'm having trouble with using the SUMPRODUCT or SUM(IF( type function, especially when the ranges I'm referring to in these functions have #NUM! references. btw  thanks Frank, Aladin for your previous advice on a similar question. Using the SUMIF function somehow aviods this problem, but not when summing using multiple criterias (ie. SUMPRODUCT or SUM(IF ( array type. Can anyone help me in avioding the #NUM!? Is it possible? Here is an example of the functions: =SUMPRODUCT((RangeName1="Customer")*(RangeName2=1 000) *TheSumRange) This returns #NUM!  I guess because all of my ranges include #NUM!. The other array type: =SUM(IF(RangeName1="Customer")* (RangeName2=1000),TheSumRange) This also returns #NUM!. I tried wrapping this with an ISERROR but couldn't get it to work  eg. =SUM(IF(ISERROR(RangeName1="Customer")* (RangeName2=1000),TheSumRange),,(RangeName1="Custo mer")* (RangeName2=1000),TheSumRange)) Assuming I want to leave the error terms within the list  can this be done? Again your help appreciated. Elijah . 
#4




Ok  getting rid of the error is probably a better approach.
The errors come from an extracted list of customer names using the array formula you provided before Frank: {=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA )3),ROW(1:1)))} I extended the formula down further than was neccessary to acomodate when the customer list growths. So that's were the #NUM! errors come into play. I guess I don't neccessarily need to do this  but I want to automate my xls as much as possible for when others use it. If there is a solution it would be helpful,  but I guess not the 'end of the world' :) I'd be happy to send you the spreadsheet if need be. Elijah ps. Biff I'll try your formula  when I remove the errors from the list. thanks "Frank Kabel" wrote in message ... Hi post your used formulas for the range names One importan thing: Don't use ranges such as A:A. Use A1:A65000 instead (if you really need such huge ranges). If your ranges itself contain a #NUM error post the formulas which create these errors. You may change them to =IF(ISERROR(your_formula)),"",your_formula)  Regards Frank Kabel Frankfurt, Germany "Elijah" schrieb im Newsbeitrag om... Hi again, I posted a similar question a week or so back  but now I'm having trouble with using the SUMPRODUCT or SUM(IF( type function, especially when the ranges I'm referring to in these functions have #NUM! references. btw  thanks Frank, Aladin for your previous advice on a similar question. Using the SUMIF function somehow aviods this problem, but not when summing using multiple criterias (ie. SUMPRODUCT or SUM(IF( array type. Can anyone help me in avioding the #NUM!? Is it possible? Here is an example of the functions: =SUMPRODUCT((RangeName1="Customer")*(RangeName2=10 00)*TheSumRange) This returns #NUM!  I guess because all of my ranges include #NUM!. The other array type: =SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange) This also returns #NUM!. I tried wrapping this with an ISERROR but couldn't get it to work  eg. =SUM(IF(ISERROR(RangeName1="Customer")*(RangeName2 =1000),TheSumRange),, (RangeName1="Customer")*(RangeName2=1000),TheSumRa nge)) Assuming I want to leave the error terms within the list  can this be done? Again your help appreciated. Elijah 
#5




Hi
use =IF(ISERROR(INDEX(Custnames,SMALL(IF(RngA="Include ",ROW(RngA)3),ROW(1: 1)))),"",INDEX(Custnames,SMALL(IF(RngA="Include",R OW(RngA)3),ROW(1:1)) )) Also entered as array formule  Regards Frank Kabel Frankfurt, Germany "Elijah" schrieb im Newsbeitrag om... Ok  getting rid of the error is probably a better approach. The errors come from an extracted list of customer names using the array formula you provided before Frank: {=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA )3),ROW(1:1)))} I extended the formula down further than was neccessary to acomodate when the customer list growths. So that's were the #NUM! errors come into play. I guess I don't neccessarily need to do this  but I want to automate my xls as much as possible for when others use it. If there is a solution it would be helpful,  but I guess not the 'end of the world' :) I'd be happy to send you the spreadsheet if need be. Elijah ps. Biff I'll try your formula  when I remove the errors from the list. thanks "Frank Kabel" wrote in message ... Hi post your used formulas for the range names One importan thing: Don't use ranges such as A:A. Use A1:A65000 instead (if you really need such huge ranges). If your ranges itself contain a #NUM error post the formulas which create these errors. You may change them to =IF(ISERROR(your_formula)),"",your_formula)  Regards Frank Kabel Frankfurt, Germany "Elijah" schrieb im Newsbeitrag om... Hi again, I posted a similar question a week or so back  but now I'm having trouble with using the SUMPRODUCT or SUM(IF( type function, especially when the ranges I'm referring to in these functions have #NUM! references. btw  thanks Frank, Aladin for your previous advice on a similar question. Using the SUMIF function somehow aviods this problem, but not when summing using multiple criterias (ie. SUMPRODUCT or SUM(IF( array type. Can anyone help me in avioding the #NUM!? Is it possible? Here is an example of the functions: =SUMPRODUCT((RangeName1="Customer")*(RangeName2=10 00)*TheSumRange) This returns #NUM!  I guess because all of my ranges include #NUM!. The other array type: =SUM(IF(RangeName1="Customer")*(RangeName2=1000),T heSumRange) This also returns #NUM!. I tried wrapping this with an ISERROR but couldn't get it to work  eg. =SUM(IF(ISERROR(RangeName1="Customer")*(RangeName2 =1000),TheSumRange),, (RangeName1="Customer")*(RangeName2=1000),TheSumRa nge)) Assuming I want to leave the error terms within the list  can this be done? Again your help appreciated. Elijah 
#6




Elijah Wrote: Ok  getting rid of the error is probably a better approach. The errors come from an extracted list of customer names using the array formula you provided before Frank: {=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA )3),ROW(1:1)))} I extended the formula down further than was neccessary to acomodate when the customer list growths. So that's were the #NUM! errors come into play. I guess I don't neccessarily need to do this  but I want to automate my xls as much as possible for when others use it. If there is a solution it would be helpful,  but I guess not the 'end of the world' :) ... Is there a particular reason for not implementing the formula system I suggested in http://www.excelforum.com/showthread.php?t=276162 to extract the customer list of interest and associated values? I'm saying this in view of the fact that you are attempting to use an inefficient (slow), nonrobust extraction formula which moreover produces #NUM! errors that thwart further processing of the extracted list. Expanding the formula in question with error trapping using ISERROR makes it doubly inefficient.  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=318967 
#7




Hi Aladin,
I started to use your implementation  but I got confused with the instructions you gave. I will definitely try it again to see if it overcomes my current problems  and will post back to you. Do you have a prepared xls example of the technique which you could email? ) I opted for the quick (and possibly inefficient) fix because at the time I had a deadline looming. I am noticing though that my xls is considerably slower than previously. Elijah "Aladin Akyurek" wrote in message ... Elijah Wrote: Ok  getting rid of the error is probably a better approach. The errors come from an extracted list of customer names using the array formula you provided before Frank: {=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA )3),ROW(1:1)))} I extended the formula down further than was neccessary to acomodate when the customer list growths. So that's were the #NUM! errors come into play. I guess I don't neccessarily need to do this  but I want to automate my xls as much as possible for when others use it. If there is a solution it would be helpful,  but I guess not the 'end of the world' :) ... Is there a particular reason for not implementing the formula system I suggested in http://www.excelforum.com/showthread.php?t=276162 to extract the customer list of interest and associated values? I'm saying this in view of the fact that you are attempting to use an inefficient (slow), nonrobust extraction formula which moreover produces #NUM! errors that thwart further processing of the extracted list. Expanding the formula in question with error trapping using ISERROR makes it doubly inefficient.  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=318967 
#8




Elijah, Example workbook is underway to you. Cheers. Aladin Elijah Wrote: Hi Aladin, I started to use your implementation  but I got confused with the instructions you gave. I will definitely try it again to see if it overcomes my current problems  and will post back to you. Do you have a prepared xls example of the technique which you could email? ) I opted for the quick (and possibly inefficient) fix because at the time I had a deadline looming. I am noticing though that my xls is considerably slower than previously. Elijah "Aladin Akyurek" wrote in message ... Elijah Wrote: Ok  getting rid of the error is probably a better approach. The errors come from an extracted list of customer names using the array formula you provided before Frank: {=INDEX(Custnames,SMALL(IF(RngA="Include",ROW(RngA )3),ROW(1:1)))} I extended the formula down further than was neccessary to acomodate when the customer list growths. So that's were the #NUM! errors come into play. I guess I don't neccessarily need to do this  but I want to automate my xls as much as possible for when others use it. If there is a solution it would be helpful,  but I guess not the 'end of the world' :) ... Is there a particular reason for not implementing the formula system I suggested in http://www.excelforum.com/showthread.php?t=276162 to extract the customer list of interest and associated values? I'm saying this in view of the fact that you are attempting to use an inefficient (slow), nonrobust extraction formula which moreover produces #NUM! errors that thwart further processing of the extracted list. Expanding the formula in question with error trapping using ISERROR makes it doubly inefficient.  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=318967  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=318967 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to use SUMIF to return sums between two values located in cells  Excel Worksheet Functions  
Where is the bug in my array?  Excel Discussion (Misc queries)  
VBA Import of text file & Array parsing of that data  Excel Discussion (Misc queries)  
how do I generate an array of correlated values  Excel Worksheet Functions  
Keeping Array values?  Excel Worksheet Functions 