SUMIFS using AND-OR logic - Excel Tip

SUMIFS function in Excel finds the sum if condition satisfies. AND - OR logic conditions can be satisfied with SUMIFS function explained below. Togglenavigation BasicExcel ExcelTips ExcelFunctions ExcelCharts VBA ExcelTip>ExcelFunctionsList>Summing>SUMIFSusingAND-ORlogicinExcel SUMIFSusingAND-ORlogicinExcel Inthisarticle,wewilllearnhowtouseAND-ORlogicin SUMIFSfunctioninExcel. ANDlogicisusedwhenallconditionsstatedneedtobesatisfied. ORlogicisusedwhenanyconditionstatedsatisfies. Insimplewords,ExcelletsyouperformthesebothlogicinSUMIFSfunction. SUMIFSwithOr ORlogicwithSUMIFSisusedwhenweneedtofindthesumifvalue1orvalue2conditionsatisfy SyntaxofSUMIFSwithORlogic =SUM(SUMIFS(sum_range,criteria_range,{"value1","value2"})) HereweneedtofindthesumofSalesrangeIf“Mike”or“Joe”occursinEmprange Use =SUM(SUMIFS(C2:C10,A2:A10,{"Mike","Joe"})) AsyoucanseewegotthesumofcombinedJoe’sandMike’ssales. SUMIFSwithAND ANDlogicwithSUMIFSfunctionisusedwhenweneedtofindthesumifvalue1andvalue2bothconditionsatisfy SyntaxofSUMIFSwithANDlogic =SUMIFS(sum_range,criteria_range1,value1,[criteria_range2,value2],..) HereweneedtofindthesumofSalesrangeIf“Joe”occursinEmprangeand“North”occursintheRegionrange Use =SUMIFS(C2:C10,A2:A10,G1,B2:B10,G2) AsyoucanseewegotthesumofjustJoe’Northregion. HopeyouunderstoodhowtouseSUMIFSfunctionwithAND-ORlogicinExcel.ExploremoreMathematicalfunctionswithlogic_testarticlesonExcelTEXTfunctionhere.Pleasefeelfreetostateyourqueryorfeedbackfortheabovearticle. RelatedArticles: 3SUMIFexampleswithOrFormulainExcel HowtoUseSUMIFFunctioninExcel HowtoUseSUMIFSFunctioninExcel HowtoSumifcellscontainspecifictextinExcel HowtoSumIfGreaterThan0inExcel SUMIFSwithdatesinExcel PopularArticles: 50ExcelShortcutstoIncreaseYourProductivity HowtousetheVLOOKUPFunctioninExcel HowtousetheCOUNTIFfunctioninExcel HowtousetheSUMIFFunctioninExcel Previous Next LeaveaReplyCancelreplyYouremailaddresswillnotbepublished.Requiredfieldsaremarked*CommentName* Email* RelatedExcelTips FindtheSUMtimevalueshavingmultiplecrit... Continue NoComments AdditionalFunctionsinAutoSum... Continue 1Comment SummingSubtotalsOnlyfromRange... Continue 2Comments 3SUMIFwithORfunctionFormulasinExcel... Continue NoComments CalculatingMonthlyandAccumulatedIncome,B... Continue NoComments Categories BasicExcelElearningExcel365FunctionsExcelBusinessTemplatesandDashboardsExcelDashboardsExcelDataExcelDateandTimeExcelErrorsExcelFunctionsExcelFunctionsListExcelGeneralExcelMacrosandVBAExcelSpanishExcelText,EditingandFormatExcelTipsandTricks TermsandConditionsofuse Theapplications/codeonthissitearedistributedasisandwithoutwarrantiesorliability.Innoeventshalltheownerofthecopyrights,ortheauthorsoftheapplications/codebeliableforanylossofprofit,anyproblemsoranydamageresultingfromtheuseorevaluationoftheapplications/code. Googleservescookiestoanalysetraffictothissite.InformationaboutyouruseofoursiteissharedwithGoogleforthatpurpose Accept Home AboutUs AdvertiseWithUs ExcelForum CommercialServices PrivacyOverview StrictlyNecessaryCookies PoweredbyGDPRplugin PrivacyOverview Thiswebsiteusescookiessothatwecanprovideyouwiththebestuserexperiencepossible.Cookieinformationisstoredinyourbrowserandperformsfunctionssuchasrecognisingyouwhenyoureturntoourwebsiteandhelpingourteamtounderstandwhichsectionsofthewebsiteyoufindmostinterestinganduseful. Youcanadjustallofyourcookiesettingsbynavigatingthetabsonthelefthandside. StrictlyNecessaryCookies StrictlyNecessaryCookieshouldbeenabledatalltimessothatwecansaveyourpreferencesforcookiesettings. disable Ifyoudisablethiscookie,wewillnotbeabletosaveyourpreferences.Thismeansthateverytimeyouvisitthiswebsiteyouwillneedtoenableordisablecookiesagain. EnableAll SaveChanges