|
Sumproduct gives #value
Hello
I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),(K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! |
Sumproduct gives #value
In article ,
Al wrote: Hello I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),( K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF($D$14:$D$500="7-F",IF($I$14:$I$500<"",IF($J$14:$J$500="A",IF(K 14 :K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0)))))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
Sumproduct gives #value
OK thanks that works for manually entering the formula.
How do I modify code range("K13").formula= "= Old Formula" (my old sumproduct formula that worked when there wasn't an N/A) I tried range("K13").FormulaArray= "= NewFormula" but I get "Unable to set FormulaArray Property of the range class" "Domenic" wrote: In article , Al wrote: Hello I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),( K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF($D$14:$D$500="7-F",IF($I$14:$I$500<"",IF($J$14:$J$500="A",IF(K 14 :K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0)))))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
Sumproduct gives #value
Try the below.....and feedback
Range("K13").FormulaArray = "=ROUNDUP(1.02*SUM(IF" & _ "(ISNUMBER(K14:K500)*($D$14:$D$500=""7-F"")*" & _ "($J$14:$J$500=""A"")*($I$14:$I$500<""""),K$14:K$ 500)),0)" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: OK thanks that works for manually entering the formula. How do I modify code range("K13").formula= "= Old Formula" (my old sumproduct formula that worked when there wasn't an N/A) I tried range("K13").FormulaArray= "= NewFormula" but I get "Unable to set FormulaArray Property of the range class" "Domenic" wrote: In article , Al wrote: Hello I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),( K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF($D$14:$D$500="7-F",IF($I$14:$I$500<"",IF($J$14:$J$500="A",IF(K 14 :K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0)))))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
Sumproduct gives #value
Ok we are getting closer. I want do the multiplication and rounding of the
individual values in K14:K500 before summing, and this seems to be summing the values and then multiplying/rounding the sum. "Jacob Skaria" wrote: Try the below.....and feedback Range("K13").FormulaArray = "=ROUNDUP(1.02*SUM(IF" & _ "(ISNUMBER(K14:K500)*($D$14:$D$500=""7-F"")*" & _ "($J$14:$J$500=""A"")*($I$14:$I$500<""""),K$14:K$ 500)),0)" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: OK thanks that works for manually entering the formula. How do I modify code range("K13").formula= "= Old Formula" (my old sumproduct formula that worked when there wasn't an N/A) I tried range("K13").FormulaArray= "= NewFormula" but I get "Unable to set FormulaArray Property of the range class" "Domenic" wrote: In article , Al wrote: Hello I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),( K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF($D$14:$D$500="7-F",IF($I$14:$I$500<"",IF($J$14:$J$500="A",IF(K 14 :K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0)))))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
Sumproduct gives #value
OK. Try the below and feedback..(multiply by 1.02; then roundup and then
sum..based on the other criterias..) Range("K13").FormulaArray = "=SUM(IF(ISNUMBER(K$14:K$500)*" & _ "($D$14:$D$500=""7-F"")*($J$14:$J$500=""A"")*($I$14:$I$500" & _ "<""""),ROUNDUP(1.02*(K$14:K$500),0)))" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: Ok we are getting closer. I want do the multiplication and rounding of the individual values in K14:K500 before summing, and this seems to be summing the values and then multiplying/rounding the sum. "Jacob Skaria" wrote: Try the below.....and feedback Range("K13").FormulaArray = "=ROUNDUP(1.02*SUM(IF" & _ "(ISNUMBER(K14:K500)*($D$14:$D$500=""7-F"")*" & _ "($J$14:$J$500=""A"")*($I$14:$I$500<""""),K$14:K$ 500)),0)" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: OK thanks that works for manually entering the formula. How do I modify code range("K13").formula= "= Old Formula" (my old sumproduct formula that worked when there wasn't an N/A) I tried range("K13").FormulaArray= "= NewFormula" but I get "Unable to set FormulaArray Property of the range class" "Domenic" wrote: In article , Al wrote: Hello I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),( K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF($D$14:$D$500="7-F",IF($I$14:$I$500<"",IF($J$14:$J$500="A",IF(K 14 :K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0)))))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
Sumproduct gives #value
Ok That one works but I have a further variant of the formula that I cannont
get to work. If I enter manually after removing the double quotes and replacing with single quotes and then ctrl+shift+enter, the formula works. however when I run the code I get "Unable to set FormulaArray Property of the range class". Range("K13").FormulaArray = "=SUM(IF(ISERROR(MATCH($C$14:$C$500,'G:\MyPath \[MyFile.xls]Sheet4'!$A$1:$A$500,0)),IF($F$14:$F$500=""7-F"",IF($I$14:$I$500=""Roger"",IF($E$14:$e$500=""Wi dget"",IF($J$14:$J$500=""s"",IF(K14:K500<""N/A"",ROUNDUP(1.02*(K$14:K$500),0))))))))" "Jacob Skaria" wrote: OK. Try the below and feedback..(multiply by 1.02; then roundup and then sum..based on the other criterias..) Range("K13").FormulaArray = "=SUM(IF(ISNUMBER(K$14:K$500)*" & _ "($D$14:$D$500=""7-F"")*($J$14:$J$500=""A"")*($I$14:$I$500" & _ "<""""),ROUNDUP(1.02*(K$14:K$500),0)))" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: Ok we are getting closer. I want do the multiplication and rounding of the individual values in K14:K500 before summing, and this seems to be summing the values and then multiplying/rounding the sum. "Jacob Skaria" wrote: Try the below.....and feedback Range("K13").FormulaArray = "=ROUNDUP(1.02*SUM(IF" & _ "(ISNUMBER(K14:K500)*($D$14:$D$500=""7-F"")*" & _ "($J$14:$J$500=""A"")*($I$14:$I$500<""""),K$14:K$ 500)),0)" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: OK thanks that works for manually entering the formula. How do I modify code range("K13").formula= "= Old Formula" (my old sumproduct formula that worked when there wasn't an N/A) I tried range("K13").FormulaArray= "= NewFormula" but I get "Unable to set FormulaArray Property of the range class" "Domenic" wrote: In article , Al wrote: Hello I am trying to use a sumproduct formula on a range that may sometime include text (specifically and exclusively N/A), but the formula is returning #value!. Once I remove any N/A with a number, the error goes away. The formula is =SUMPRODUCT(--($D$14:$D$500="7-F"),--($J$14:$J$500="A"),--($I$14:$I$500<""),( K14:K500<"N/A"),ROUNDUP(1.02*(K$14:K$500),0)) Thanks! Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF($D$14:$D$500="7-F",IF($I$14:$I$500<"",IF($J$14:$J$500="A",IF(K 14 :K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0)))))) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com