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