Sumproduct gives #value

[Al]

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!

 

[Domenic]

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(K14
:K500<>"N/A",ROUNDUP(1.02*(K$14:K$500),0))))))

 

[Al]

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"

 

[Jacob Skaria]

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

 

[Al]

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]

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

 

[Al]

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=""Widget"",IF($J$14:$J$500=""s"",IF(K14:K500<>""N/A"",ROUNDUP(1.02*(K$14:K$500),0))))))))"