Multiple column array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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)

Jim said:
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
Click to expand...
columns in an array. How do I do that so that my formula works?
 
Thanks!

Jim
--
Art Production Manager
Gas Powered Games


Aladin Akyurek said:
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)

Jim said:
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
Click to expand...
columns in an array. How do I do that so that my formula works?
Click to expand...
 
Back
Top