+ Reply to Thread
Results 1 to 4 of 4

Multiple Criteria Sumifs in Multiple Columns

  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Multiple Criteria Sumifs in Multiple Columns

    Hello All,

    I am trying to improve how I write my formulae. In this case I have replaced this horribly long formula:

    =SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3006,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3031,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3035,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,3,TransactionCode,3006,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,3,TransactionCode,3031,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,3,TransactionCode,3035,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"SCR",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3006,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"SCR",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3031,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"SCR",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3035,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"SCR",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,3,TransactionCode,3006,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"SCR",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,3,TransactionCode,3031,ChargeOff,"OK")
    +SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"SCR",TransactionSource,"MC",ClassCode,"PR",TransactionCategory,3,TransactionCode,3035,ChargeOff,"OK")

    with this nice shortened version:

    {=SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},TransactionCode,{3006;3035;3031},ChargeOffCredits,{"OK"},CAAPartner,A10,ChargeOffCredits,"OK"))}


    I have pieced this together using information on various forums and it appears to work, but I have a question:

    What is the difference between using a comma and semi-colon in this new formula?



    Thank you all for your time :-)
    Alan
    Last edited by arowberry; 03-04-2014 at 06:08 PM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Multiple Criteria Sumifs in Multiple Columns

    Hello Alan,

    when you separate with commas that's the equivalent of a row in Excel (and you could replace {"CRT","SCR"} in the formula with a two cell horizontal range including those values, e.g. A1:B1) and separating with semi-colons is the equivalent of a column. Your SUMIFS function then gives the result as a 2*3 matrix containing all 6 possible combinations (and SUM sums that matrix to give the total)

    btw you don't need curly brackets round the single values....and you don't need CTRL+SHIFT+ENTER as written, i.e. could be

    =SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,"MC",TransactionCode,{3006;3035;3031},ChargeOffCredits,"OK",CAAPartner,A10,ChargeOffCredits,"OK"))

    Also note that you cannot add more "multi-criteria" conditions

    Edit: and this doesn't replace the original, that does have 3 multi-criteria conditions so you would need at least two of the shorter versions to replace that
    Last edited by daddylonglegs; 03-04-2014 at 05:12 PM.
    Audere est facere

  3. #3
    Registered User
    Join Date
    04-30-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Multiple Criteria Sumifs in Multiple Columns

    Thank you very much for the incredibly fast response!

    I think I understand, but I am a beginner at using array equations. Am I correct in saying that you cannot use 3 multi-criteria conditions as this would result in a three dimensional matrix?


    Edit: and this doesn't replace the original, that does have 3 multi-criteria conditions so you would need at least two of the shorter versions to replace that
    Would this be a replacement for the original?

    =SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},TransactionCode,{3006;3035;3031},TransactionCategory,1,ChargeOffCredits,"OK",CAAPartner,A5,ChargeOffCredits,"OK"))
    +SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},TransactionCode,{3006;3035;3031},TransactionCategory,3,ChargeOffCredits,"OK",CAAPartner,A5,ChargeOffCredits,"OK"))

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Multiple Criteria Sumifs in Multiple Columns

    Yes, you can only have a 2 dimensional matrix in this context, so for 3 or more multi-criteria conditions your choices are to stick with SUMIFS and have more than one SUMIFS function.....or you could switch to SUMPRODUCT using ISNUMBER/MATCH - you might get a shorter formula with SUMPRODUCT but , especially for large amounts of data, SUMIFS will probably be more efficient

    for 2 SUMIFS you can save a SUM function by using this form

    =SUM(SUMIFS1,SUMIFS2)

    ...so that would be...

    =SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,"MC",TransactionCode,{3006;3035;3031},TransactionCategory,1,ChargeOffCredits,"OK",CAAPartner,A5,ChargeOffCredits,"OK"),SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,"MC",TransactionCode,{3006;3035;3031},TransactionCategory,3,ChargeOffCredits,"OK",CAAPartner,A5,ChargeOffCredits,"OK"))

    or in this particular case, if Transaction Category is always an integer you could get creative and use a single SUMIFS with several Transaction Category criteria, i.e.

    =SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,"MC",TransactionCode,{3006;3035;3031},TransactionCategory,">=1",TransactionCategory,"<=3",TransactionCategory,"<>2",ChargeOffCredits,"OK",CAAPartner,A5,ChargeOffCredits,"OK"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help understanding sumifs columns with multiple criteria
    By igoodable in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2014, 02:27 PM
  2. Sumifs with multiple criteria in multiple columns
    By Ganesh7299 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-30-2013, 01:30 AM
  3. [SOLVED] SUMIF or SUMIFS adding time where criteria is in multiple columns
    By 66ev in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-14-2013, 02:21 PM
  4. Sumifs with multiple criteria for multiple names
    By mcayea in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 05-07-2013, 10:55 AM
  5. Replies: 2
    Last Post: 01-23-2013, 06:25 AM

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