Hi I have a very long sumproduct formula and excel won't take it, 'The specified formula cannot be entered because it contains more values, references and/or names than are allowed in the current file format."
My formula consists of sumproducts with 3 variables and repeats about 30 times.
Sample:
=SUMPRODUCT(--(Sheet1!$F$1:$F$9999="APPLE"),--(Sheet1!$G$1:$G$9999="GOOD"),--(Sheet1!$B$1:$B$9999="SOLD"))+SUMPRODUCT(--(Sheet1!$F$1:$F$9999="ORANGE"),--(Sheet1!$G$1:$G$9999="GOOD"),--(Sheet1!$B$1:$B$9999="SOLD"))
Anyway to shorten this so I can get it into one cell? THANKS!
My formula consists of sumproducts with 3 variables and repeats about 30 times.
Sample:
=SUMPRODUCT(--(Sheet1!$F$1:$F$9999="APPLE"),--(Sheet1!$G$1:$G$9999="GOOD"),--(Sheet1!$B$1:$B$9999="SOLD"))+SUMPRODUCT(--(Sheet1!$F$1:$F$9999="ORANGE"),--(Sheet1!$G$1:$G$9999="GOOD"),--(Sheet1!$B$1:$B$9999="SOLD"))
Anyway to shorten this so I can get it into one cell? THANKS!