Shorten sumproduct formula

[Andre Croteau]

Hello,

Is there a way to use a sumproduct formula looking at non-contiguous cells?

In cell a1, I have this formula:

=SUMPRODUCT((G14<G15)*(H14>G15)*(LEN(G14)>0))+SUMPRODUCT((G16<G15)*(H16>G15)
*(LEN(G16)>0))

I tried the following with no success:

SUMPRODUCT(((G14,G16)<G15)*((H14,H16)>G15)*(LEN((G14,G16))>0), but gives me
a #VALUE

Any suggestions?

Thanks

André

 

[Bob Phillips]

André,

Not sure why you need to shorten it, but is this any good (not the way you
were thinking)

=((G14<G15)*(H14>G15)*(LEN(G14)>0))+((G16<G15)*(H16>G15)*(LEN(G16)>0))

--

HTH

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