Results 1 to 3 of 3

Excel Formula Error

This is a discussion on Excel Formula Error within the Excel Questions forums, part of the Question Forums category; Hi, I get this error went entering a formula 'The specified formula cannot be entered because it contains more values, ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Toronto, CANADA
    Posts
    127

    Default Excel Formula Error

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

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,652

    Default Re: Excel Formula Error

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

  3. #3
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,924

    Default Re: Excel Formula Error

    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.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com