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

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

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