Help On SUMPRODUCT Formula

[Joe Gieder]

I have this formula: =SUMPRODUCT(--('Priced BOM'!G3:G874="Make"),--('Priced
BOM'!S3:S874=""))+SUMPRODUCT(--('Priced BOM'!$G$3:$G$874="-"),--('Priced
BOM'!$S$3:$S$874=""))+SUMPRODUCT(--('Priced BOM'!$C$3:$C$874="R"),--('Priced
BOM'!$S$3:$S$874=""))+SUMPRODUCT(--('Priced BOM'!$C$3:$C$874="I"),--('Priced
BOM'!$S$3:$S$874=""))+SUMPRODUCT(--('Priced BOM'!$C$3:$C$874="K"),--('Priced
BOM'!$S$3:$S$874=""))+COUNTIF('Priced BOM'!S3:S874,">0")+19
and it works fine. I tried to shorten it to:
=SUMPRODUCT(--(OR('Priced BOM'!G3:G874="Make",'Priced
BOM'!$G$3:$G$874="-",'Priced BOM'!$C$3:$C$874="R",'Priced
BOM'!$C$3:$C$874="I",'Priced BOM'!$C$3:$C$874="K")),--('Priced
BOM'!$S$3:$S$874=""))+COUNTIF('Priced BOM'!S3:S874,">0")+19
but it gives me a #VALUE error. Can the original formula be shortened? What
could I have done wrong with the shortened formula?

Thanks in advance for your help
Joe

 

[Aladin Akyurek]

I believe the formula as it stands would lead to counting the same
record twice: For example: G3 = Make, S3 = "" and C3 = R and S3 = "".

It's probably better to describe the task that you want to accomplish
instead of eliciting comments on a potentially wrong formula.

 

[Bob Phillips]

I think this maintains your tests. It works, but you will need to verify

=SUMPRODUCT(--(ISNUMBER(MATCH($G$3:$G$8,{"Make","-"},0))),--(ISNUMBER(MATCH(
$C$3:$C$8,{"R","I","K"},0))),--($S$3:$S$8=""))

--

HTH

RP
(remove nothere from the email address if mailing direct)