Thread: Help On SUMPRODUCT Formula

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

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.


Joe Gieder wrote:
> 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
>
>

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)


"Joe Gieder" <JoeGieder@discussions.microsoft.com> wrote in message
news:84C11D78-96B1-431F-8D40-8090BB3AE4F7@microsoft.com...
> 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
>
>