+ Reply to Thread
Results 1 to 7 of 7

SUMIFS with a criteria range ?

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2007
    Posts
    57

    SUMIFS with a criteria range ?

    Hi guys,

    I have the following formula that I did:

    =SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "delivered")+SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "shipped")

    As you can see I have exactly same thing in the first SUMIFS function, and the second one except for the "delivered" and "shipped" criteria. Now, what I would like to do is to create a range that would be used instead of a single criteria.

    Actually, I have more than 20 criteria but I don't want to do 20 separate SUMIFS and then adding them. Best would be if I could select a range of criterias (I would like to be updating that list easily in the future).

    I'd much appreciate !

  2. #2
    Registered User
    Join Date
    12-19-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excell 2007
    Posts
    3

    Re: SUMIFS with a criteria range ?

    I think you can shorten the formula by using a Sumproduct instead of Sumifs but even the Sumproduct will require all criteria to be built into the formula. Here's an example.

    =SUMPRODUCT((Sheet2!B:B=1)*(Sheet3!A:A=Sheet3!A5)*(Sheet2!X:X="LOL")*((Sheet2!R:R="Delivered")+(Sheet2!R:R="shipped")+(Sheet2!R:R="Pending"))*Sheet2!L:L)

    I'm not the most experienced Excel user so I'm sure there's a better way to accomplish but this might work in the meantime.

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: SUMIFS with a criteria range ?

    Thanks. Unfortunately it seems like this actually take more memory for my excel

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: SUMIFS with a criteria range ?

    Try this untested formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Registered User
    Join Date
    09-07-2012
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: SUMIFS with a criteria range ?

    Thanks, this actually works better !

    Last question (I found about this option today) - can I just simply name some range i.e "Range1" and then put it into the formula ?
    like - =Sum(SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R,"Range1") because it doesn't seem to work...

  6. #6
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: SUMIFS with a criteria range ?

    use SUMPRODUCT instead of SUM as the outermost function, in that case.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: SUMIFS with a criteria range ?

    Quote Originally Posted by vemix View Post
    can I just simply name some range i.e "Range1" and then put it into the formula
    Don't refer the named range within in Double Quotes.

+ 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