|
SumProduct Formula Error
I am using the following formula:
=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G2:$G3702<31),$D2:$D3702) Because the cells in the columns use a formula that sometimes returns a #N/A result, the result of the above formula is always #N/A. Is there a way to alter the formula so that is only adds the numbers and returns the sum of the D column for all the cells that meet the enclosed criteria? |
You would be better off if you fixed the #N/A like
=IF(ISNA(formula),"",formula) regardless in what column do they (N/A) appear? If they are in one particular column you could just use the IF for that particular column, otherwise you could use something like =SUM((IF(ISNUMBER($A2:$A37),$A2:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2: $A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Josh O." wrote in message ... I am using the following formula: =SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G 2:$G3702<31),$D2:$D3702) Because the cells in the columns use a formula that sometimes returns a #N/A result, the result of the above formula is always #N/A. Is there a way to alter the formula so that is only adds the numbers and returns the sum of the D column for all the cells that meet the enclosed criteria? |
Okay, is there a way to fix a #VALUE!? In the same cell? The formulas in
the cell range can return "#N/A" if a customer number is found but not an invoice number, and "#VALUE!" if the customer number is not found. I tried the formula you have below (with the correct cell ranges), but I am getting a #VALUE! error. Is that because there are #VALUE! errors in the cell ranges? "Peo Sjoblom" wrote: You would be better off if you fixed the #N/A like =IF(ISNA(formula),"",formula) regardless in what column do they (N/A) appear? If they are in one particular column you could just use the IF for that particular column, otherwise you could use something like =SUM((IF(ISNUMBER($A2:$A37),$A2:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2: $A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Josh O." wrote in message ... I am using the following formula: =SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G 2:$G3702<31),$D2:$D3702) Because the cells in the columns use a formula that sometimes returns a #N/A result, the result of the above formula is always #N/A. Is there a way to alter the formula so that is only adds the numbers and returns the sum of the D column for all the cells that meet the enclosed criteria? |
Assuming the values error come from the column D
=SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702847300000 ))*(IF(ISNUMBER($A2:$A3702 ),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G 2:$G37020))*(IF(ISNUMBER( $D2:$D3702),$D2:$D3702))) also array entered value errors come from calculated text, (for exam;e =1+"a") you should be able to fix that as well, that way you could use your original formula -- Regards, Peo Sjoblom "Josh O." wrote in message ... Okay, is there a way to fix a #VALUE!? In the same cell? The formulas in the cell range can return "#N/A" if a customer number is found but not an invoice number, and "#VALUE!" if the customer number is not found. I tried the formula you have below (with the correct cell ranges), but I am getting a #VALUE! error. Is that because there are #VALUE! errors in the cell ranges? "Peo Sjoblom" wrote: You would be better off if you fixed the #N/A like =IF(ISNA(formula),"",formula) regardless in what column do they (N/A) appear? If they are in one particular column you could just use the IF for that particular column, otherwise you could use something like =SUM((IF(ISNUMBER($A2:$A37),$A2:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2: $A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Josh O." wrote in message ... I am using the following formula: =SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G 2:$G3702<31),$D2:$D3702) Because the cells in the columns use a formula that sometimes returns a #N/A result, the result of the above formula is always #N/A. Is there a way to alter the formula so that is only adds the numbers and returns the sum of the D column for all the cells that meet the enclosed criteria? |
How do fix the value error? It comes from a vlookup formula for customers
that are not on a particular report. "Peo Sjoblom" wrote: Assuming the values error come from the column D =SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702847300000 ))*(IF(ISNUMBER($A2:$A3702 ),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G 2:$G37020))*(IF(ISNUMBER( $D2:$D3702),$D2:$D3702))) also array entered value errors come from calculated text, (for exam;e =1+"a") you should be able to fix that as well, that way you could use your original formula -- Regards, Peo Sjoblom "Josh O." wrote in message ... Okay, is there a way to fix a #VALUE!? In the same cell? The formulas in the cell range can return "#N/A" if a customer number is found but not an invoice number, and "#VALUE!" if the customer number is not found. I tried the formula you have below (with the correct cell ranges), but I am getting a #VALUE! error. Is that because there are #VALUE! errors in the cell ranges? "Peo Sjoblom" wrote: You would be better off if you fixed the #N/A like =IF(ISNA(formula),"",formula) regardless in what column do they (N/A) appear? If they are in one particular column you could just use the IF for that particular column, otherwise you could use something like =SUM((IF(ISNUMBER($A2:$A37),$A2:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2: $A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Josh O." wrote in message ... I am using the following formula: =SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G 2:$G3702<31),$D2:$D3702) Because the cells in the columns use a formula that sometimes returns a #N/A result, the result of the above formula is always #N/A. Is there a way to alter the formula so that is only adds the numbers and returns the sum of the D column for all the cells that meet the enclosed criteria? |
I don't think vlookup can retrun a value erro unless you make a calculation
with the result or if there are value errors in the lookup table itself. If you are making a calculation like n*vlookup and vlookup sometimes returns a "" or a text? =IF(ISTEXT(vlookup),0,vlookup) or if the vlookup gives the error =IF(ISERROR(vlookup),0,vlookup) something like that -- Regards, Peo Sjoblom "Josh O." wrote in message ... How do fix the value error? It comes from a vlookup formula for customers that are not on a particular report. "Peo Sjoblom" wrote: Assuming the values error come from the column D =SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702847300000 ))*(IF(ISNUMBER($A2:$A3702 ),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G 2:$G37020))*(IF(ISNUMBE R( $D2:$D3702),$D2:$D3702))) also array entered value errors come from calculated text, (for exam;e =1+"a") you should be able to fix that as well, that way you could use your original formula -- Regards, Peo Sjoblom "Josh O." wrote in message ... Okay, is there a way to fix a #VALUE!? In the same cell? The formulas in the cell range can return "#N/A" if a customer number is found but not an invoice number, and "#VALUE!" if the customer number is not found. I tried the formula you have below (with the correct cell ranges), but I am getting a #VALUE! error. Is that because there are #VALUE! errors in the cell ranges? "Peo Sjoblom" wrote: You would be better off if you fixed the #N/A like =IF(ISNA(formula),"",formula) regardless in what column do they (N/A) appear? If they are in one particular column you could just use the IF for that particular column, otherwise you could use something like =SUM((IF(ISNUMBER($A2:$A37),$A2:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2: $A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Josh O." wrote in message ... I am using the following formula: =SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G 2:$G3702<31),$D2:$D3702) Because the cells in the columns use a formula that sometimes returns a #N/A result, the result of the above formula is always #N/A. Is there a way to alter the formula so that is only adds the numbers and returns the sum of the D column for all the cells that meet the enclosed criteria? |
| All times are GMT +1. The time now is 06:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com