Excel Formula Error

[natetc]

Hi,

I get this error went entering a formula

'The specified formula cannot be entered because it contains more values, references and/or names than are allowed in the current file format.'

I am entering a SUMPRODUCT formula with over 50 statements, example

=SUMPRODUCT(--(A1:A100="Fruit"),--(B1:B100="Oranges"))+SUMPRODUCT(--(A1:A100="Meat"),--(B1:B100="Beef"))

 

[mikerickson]

What is the result you want from that monster formula?
DCOUNT might be an easier way to count rows meeting 50 multiple conditions.

 

[schielrn]

You could break them into multiple named ranges and then add all the named ranges together. Such as:

NameRange1 = SUMPRODUCT(--(A1:A100="Fruit"),--(B1:B100="Oranges"))
NameRange2 = SUMPRODUCT(--(A1:A100="Meat"),--(B1:B100="Beef"))

Then do something like =NameRange1+NameRange2

And so forth. There is a limit on the number of characters in a formula I believe it is 1,024, but not positive.

Hope that helps.