+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Sumifs formula lookup multiple criteria in criteria range

  1. #1
    Registered User
    Join Date
    08-27-2010
    Location
    Boston, Ma
    MS-Off Ver
    Excel 2003
    Posts
    2

    Sumifs formula lookup multiple criteria in criteria range

    I have a sumifs formula that is working correctly, but is extremely long and I am hoping to get it shortened. The formula I have set up is as follows:

    Please Login or Register  to view this content.
    Now this isn't the full formula, but this shows how it looks. The "Test!$B$1" part of the sumifs formula goes from B$1$ all the way to B$30$, so the formula is very long. In order to shorten the formula, I have been trying to replace the Second Criteria ("Test!$B$1") in the Second Criteria Range ("Data!$B$2:$B$39654") of the sumifs formula with some sort of constant value, but can't seem to figure it out. Is there anyway to shorten this formula? Or is there a different variation I could use?

    I appreciate any and all help.

    Thank-you

  2. #2
    Registered User
    Join Date
    03-17-2011
    Location
    Illinois
    MS-Off Ver
    2007-2010
    Posts
    22

    Re: Sumifs formula lookup multiple criteria in criteria range

    Try naming the ranges instead of using tab and cell references.
    It's located in the formula tab, and you can select your range, and "create from selection". Do that for all your ranges, and when constructing your now shortened formula, replace the range locations with their new name.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1