Multiple column array

[Jim]

I can get the following formula to work:
=SUMPRODUCT(--(Content!F2:Content!F475="Marcus"),--(Content!G2:Content!G475="need"),--(Content!M2:Content!M475="M4"),Content!L2:Content!L475)

But if I expand the "need" array like:
=SUMPRODUCT(--(Content!F2:Content!F475="Marcus"),--(Content!F2:Content!J475="need"),--(Content!M2:Content!M475="M4"),Content!L2:Content!L475)

It stops working. I would love to have the entire colums or set of columns in an array. How do I do that so that my formula works?

Thanks
Jim

 

[Jim]

I was able to get it to work by using this:
=SUM(SUMPRODUCT(--(Content!F2:F5000="Marcus"),--(Content!I2:I5000="need"),--(Content!M2:M5000="M4"),Content!L2:L5000))+(SUMPRODUCT(--(Content!F2:F5000="Marcus"),--(Content!H2:H5000="need"),--(Content!M2:M5000="M4"),Content!L2:L5000))+(SUMPRODUCT(--(Content!F2:F5000="Marcus"),--(Content!I2:I5000="need"),--(Content!M2:M5000="M4"),Content!L2:L5000))+(SUMPRODUCT(--(Content!F2:F5000="Marcus"),--(Content!J2:J5000="need"),--(Content!M2:M5000="M4"),Content!L2:L5000))

But there has to be a cleaner way! Any ideas?

Thanks
Jim

 

[Aladin Akyurek]

You have to switch from the native comma systax to the star syntax because
you mix vectors with a (multi-column) matrix:

=SUMPRODUCT((Content!F2:F475="Marcus")*(Content!F2:J475="need")*(Content!M2:
M475="M4")*Content!L2:L475)

I can get the following formula to work:
=SUMPRODUCT(--(Content!F2:Content!F475="Marcus"),--(Content!G2:Content!G475=
"need"),--(Content!M2:Content!M475="M4"),Content!L2:Content!L475)

But if I expand the "need" array like:
=SUMPRODUCT(--(Content!F2:Content!F475="Marcus"),--(Content!F2:Content!J475=
"need"),--(Content!M2:Content!M475="M4"),Content!L2:Content!L475)

It stops working. I would love to have the entire colums or set of

columns in an array. How do I do that so that my formula works?

 

[Jim]

Thanks!

Jim
--
Art Production Manager
Gas Powered Games

You have to switch from the native comma systax to the star syntax because
you mix vectors with a (multi-column) matrix:

=SUMPRODUCT((Content!F2:F475="Marcus")*(Content!F2:J475="need")*(Content!M2:
M475="M4")*Content!L2:L475)

I can get the following formula to work:
=SUMPRODUCT(--(Content!F2:Content!F475="Marcus"),--(Content!G2:Content!G475=
"need"),--(Content!M2:Content!M475="M4"),Content!L2:Content!L475)

But if I expand the "need" array like:
=SUMPRODUCT(--(Content!F2:Content!F475="Marcus"),--(Content!F2:Content!J475=
"need"),--(Content!M2:Content!M475="M4"),Content!L2:Content!L475)

It stops working. I would love to have the entire colums or set of

columns in an array. How do I do that so that my formula works?