如何在Excel中將IF函數與AND，OR和NOT一起使用？

在Excel中，IF函數對於檢查單元格是否滿足條件並根據評估結果（True和False）返回不同的值非常有用。

並將IF函數與AND，OR，NOT函數結合使用，將改變檢查單元格的條件 ... Note:TheotherlanguagesofthewebsiteareGoogle-translated.BacktoEnglish登入  登錄到您的帳戶登入記住我忘記密碼？or重設密碼為您的帳戶，請輸入電子郵件地址。

驗證碼將發送給您。

當您收到驗證碼，你將可以選擇為您的帳戶新密碼。

提交註冊  orExtendOffice商品OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook下載OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook如何安裝或卸載最終用戶許可協議購買OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook資源Excel的技巧和竅門Word的技巧和竅門Outlook的技巧和竅門Excel函數Excel公式Excel圖表Outlook教程聯絡我們在線教程 OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook新聞和更新 OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook搜尋搜索更多關於我們我們的團隊用戶感言我們的客戶說得到幫助？ 檢索許可證丟失許可證？報告錯誤錯誤報告論壇在論壇中發帖聯絡我們通過電子郵件與我們聯繫語言简体中文繁體中文FrançaisDeutschPortuguêsItalianoČeština‎DanskNederlandsالعربيةՀայերենΕλληνικάMagyarBahasaIndonesiaGaelige日本語한국어PolskiRomânăРусскийSlovenščinaEspañolSvenskaไทยTürkçeУкраїнськаTiếngViệtCymraegExtendOffice如何在Excel中將IF函數與AND，OR和NOT一起使用？在Excel中，IF函數對於檢查單元格是否滿足條件並根據評估結果（True和False）返回不同的值非常有用。

並將IF函數與AND，OR，NOT函數結合使用，將改變檢查單元格的條件。

在這裡，我將介紹如何使用IF和AND函數，IF和OR函數以及如何在Excel中組合多個IF函數。

如何在Excel中使用IF函數如何在Excel中使用If函數和AND函數如何在Excel中使用IF函數和OR函數如何在Excel中的一個公式中組合多個IF函數假設我們有一個銷售表，如下面的屏幕截圖所示，現在我們要評估銷售價值並將其分組為“非凡“”低“”中間“和”高".現在，將行標題和列標題複製到一個空白區域（在我們的示例中為範圍A16：E28），如以下屏幕截圖所示。

如何在Excel中使用IF函數Excel中IF函數的基本形式如下所示：=IF（邏輯測試，value_if為true，value_if_false）在我們的情況下，我們要檢查銷售量是否低。

如果單元格B2中的值等於或小於80，則返回文本“低”；如果單元格B2中的值大於80，則返回空白。

輸入公式=IF（B2<=80，“低”，“”）到單元格B17中，然後將“填充手柄”拖動到範圍B17：E28。

請參閱以下屏幕截圖：輕鬆地將多個工作表/工作簿/CSV文件組合到一個工作表/工作簿中將來自不同工作簿的數十張表合併為一張表可能很麻煩。

但是使用KutoolsforExcel的合併（工作表和工作簿）實用程序，只需單擊幾下就可以完成！全功能免費試用30天！如何在Excel中使用If函數和AND函數如果要同時檢查一個單元格是否滿足多個條件，可以在Excel中組合IF函數和AND函數。

Excel中AND函數的基本形式是=AND（邏輯1，邏輯2，邏輯3，…等）現在，在我們的案例中，我們要同時檢查一個銷售量是否大於80而小於160，如果是，則返回“中間”文本，如果否，則返回空白。

輸入公式=IF（AND（B2>80，B2<160），“Middle”，“”）進入單元格B17，然後將“填充手柄”拖動到範圍B17：E28。

請參閱以下屏幕截圖：如何在Excel中使用IF函數和OR函數如果要檢查單元格是否滿足多個條件之一，可以在Excel中組合IF函數和OR函數。

Excel中OR功能的基本形式是=OR（邏輯1，邏輯2，邏輯3，等等）在我們的示例中，我們要查找大於180或小於20的非常規銷售量。

如果銷售量是非常規的，則返回文本“非常規”，如果沒有返回空白。

輸入公式=IF（OR（B2>180，B2<20），“非凡”，“”）進入單元格B17，然後將“填充手柄”拖動到範圍B17：E28。

請參閱以下屏幕截圖：如何在Excel中將多個IF函數合併為一個公式在我們的示例中，如果要使用一個公式評估所有銷量，則可以應用複雜的IF函數。

如果銷售量等於或小於20，則返回文本“非常規”；如果銷售量等於或小於80，則返回文本“低”；如果等於或小於160，則返回文本“中”；如果等於或小於200，則返回文本“高”。

輸入公式=IF（B2<=20，“非凡”，IF（B2<=80，“低”，IF（B2<=160，“中”，IF（B2<=200，“高”，“”）））））進入單元格B17，然後將“填充手柄”拖動到範圍B17：E28。

請參閱以下屏幕截圖：公式太難記了嗎？將公式另存為“自動文本”條目，以供日後再次使用！閱讀全文...    免費試用最佳辦公效率工具KutoolsforExcel解決了您的大部分問題，並將您的生產力提高了80%重用:快速插入複雜的公式，圖表 以及您以前使用過的任何東西；加密單元帶密碼創建郵件列表並發送電子郵件...超級公式欄（輕鬆編輯多行文本和公式）；閱讀版式（輕鬆讀取和編輯大量單元格）；粘貼到過濾範圍...合併單元格/行/列不會丟失數據；拆分單元格內容；合併重複的行/列...防止細胞重複；比較範圍...選擇重複或唯一行;選擇空白行（所有單元格都是空的）；超級查找和模糊查找在許多工作簿中；隨機選擇...確切的副本多個單元格，無需更改公式參考；自動創建參考到多張紙；插入項目符號，複選框等...提取文字，添加文本，按位置刪除，刪除空間;創建和打印分頁小計；在單元格內容和註釋之間轉換...超級濾鏡（將過濾方案保存並應用於其他工作表）；高級排序按月/週/日，頻率及更多；特殊過濾器用粗體，斜體...結合工作簿和工作表;根據關鍵列合併表；將數據分割成多個工作表;批量轉換xls，xlsx和PDF...300多項強大功能。

支持Office/Excel2007-2019和365。

支持所有語言。

在您的企業或組織中輕鬆部署。

完整功能30天免費試用。

60天退款保證。

閱讀更多...免費下載...採購... Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。

在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。

將您的工作效率提高50%，每天為您減少數百次鼠標點擊！閱讀更多...免費下載...採購...  56CommentsLoginSortbyNewestBestPopularNewestOldestSaysomethinghere...symbolsleft.YouareguestLoginNoworpostasaguest,butyourpostwon'tbepublishedautomatically.PostLoadingcomment...Thecommentwillberefreshedafter00:00.Topostasaguest,yourcommentisunpublished.ahmed.dba·1monthsago HIEVERYONEINEEDHELPINTHISSYNTEX=IF(AND(AJ=1250,AJ*2.5%),IF(AND(AJ>1250,AJ<=2500),AJ*10%,IF(AND(AJ>2500,AJ<=3750),AJ*15%,IF(AND(AJ>3750,AJ<=11666),AJ*20%))))THISFOURMAILGIVEME #NAME WHERETHEEROOReplyTopostasaguest,yourcommentisunpublished.AmandaLee·28daysago Hi ahmed.dba,[email protected]?Andifyouhaveprivateinformationinthefile,pleasedeletethem.AmandaReplyTopostasaguest,yourcommentisunpublished.Andrea·1monthsago IamtryingtowriteaformulatotellmeifIamavailableorbusyonspecificdaysanddates(Iamalwaysbusyoncertaindaysoftheweek,butthenalsoonspecificdates).WhatIhavesofaris:=IF(OR(A226="Monday",A226="Wednesday",A226="Thursday",A226="Friday",$Z$226:$Z$255=B226),"Busy","Available")ColumnsAisthedayoftheweekastext,columnBisthedayofthemonthasanumber, ReplyTopostasaguest,yourcommentisunpublished.AmandaLee·28daysago HiAndrea,[email protected]?Andifyouhaveprivateinformationinthefile,pleasedeletethem.AmandaReplyTopostasaguest,yourcommentisunpublished.Mayer·11monthsago IneedhelptomakeaformulaQuantityCount1Count2Count31083710874108740Ifcount=0,makecount2-Quantity,ifis<>0,makecount3-Quantity,andifcount2andcount3=0,makecount1-QuantityReplyTopostasaguest,[email protected]·2yearsago IneedhelpcombiningtheseIFformula: IFD7<>C$1"OOP","Current" IFD7=C$1andS7=0,T7>0,"NoRev" IFD7=C$1andS7<0,T7>0,"Loss" IFD7=C$1andS7<0,R=0,"NoCost" IFD7=C$1andX7<=30%,"HighGP" IFD7-C$1andX7>=5%,"LowGP"ReplyTopostasaguest,[email protected]·2yearsago Guys,couldyoupleasehelpmewiththislogic: =IFfr2=31(FT4>30,18000,FR4*FQ4)andIFfr2=30(FT4>29,18000,FR4*FQ4) iwanttocombinethis. plzreplyearly.ReplyTopostasaguest,yourcommentisunpublished.David·2yearsago Hithere,IneedthistoreturnthevalueinthecolumnB6:B28,wheneitheranHoranSareintheboxesI6:I28,worksfinewithouttheORandjustH,butnotwiththeORfunction,canyoupossiblyhelp?=IF(AF8=I5,TEXTJOIN(",",FALSE,IF(OR(I6:I28="H",I6:I28="S"),B6:B28,""))) Manythanks DavidReplyTopostasaguest,yourcommentisunpublished.Arturo·2yearsago Guys,couldyoupleasehelpmewiththislogic: IfA1beginswith2zeros(00)returnthenumber1,ifnotreturnthenumber2ReplyTopostasaguest,yourcommentisunpublished.Alice·2yearsago 8. TobeaBunkLeader,astaffmembermusthaveover4serviceyearsandhavecompletedleadershiptraining. IncellL2,enteraformulausingtheIFandANDfunctions,aswellasstructuredreferencestodetermineifAdamMoriartycanbeabunkleader. a. ThelogicaltestintheIFfunctionshoulddetermineifthestaffmember’sServiceYearsisgreaterthan4ANDthestaffmember’sLeadershipTrainingstatusis“Yes”.RemembertouseastructuredreferencetotheServiceYearsandtheLeadershipTrainingcolumns. b. ThefunctionshouldreturnthetextYesifastaffmembermeetsbothofthosecriteria. c. ThefunctionshouldreturnthetextNoifastaffmembermeetsnoneoronlyoneofthosecriteria.ReplyTopostasaguest,yourcommentisunpublished.kellytte·2yearsago HiAlice, SupposingthenumberofserviceyearisplacedinCellB2,theleadershiptrainingstatsisplacedinCellC2,youcanusethisformula=IF(AND(B2>4,C2="Yes"),"Yes","No") ReplyTopostasaguest,yourcommentisunpublished.Nicolae·2yearsago hello! Ineedyourbrilliantmindstoresolveaproblem: ifa1=blank&b1=blank&c1=blank&d1=blankthenresult1 ifa1=blank&b1=blank&c1=blank&d1>0thenresult2 ifa1=blank&b1=blank&c1>0&d1>0thenresult3 ifa1=blank&b1>0&c1>0&d1>0thenresult4 ifa1>0&b1>0&c1>0&d1>0thenresult5 Canyoupleasehelpmewithaformula? i'vetriedwithif/and,if/orbutitseamsican'tgetaresult. Thanksinadvance!ReplyTopostasaguest,yourcommentisunpublished.upsss·2yearsago =IF(AND(ISBLANK(A1);ISBLANK(B1);ISBLANK(C1);ISBLANK(D1));1;IF(AND(ISBLANK(A1);ISBLANK(B1);ISBLANK(C1);D1>0);2;IF(AND(ISBLANK(A1);ISBLANK(B1);C1>0;D1>0);3;IF(AND(ISBLANK(A1);B1>0;C1>0;D1>0);4;5))))ReplyTopostasaguest,[email protected]·2yearsago Needsomehelpwithlogic.Iwantedtocheckthefirstcellfordataifithasdatathenitwillreturnthatcell;ifitdoesn'thavedatathenitwillreturnadifferentcell.Butifthesecondcellisblankthenitshouldreturnamsg.Canyoupleasehelp.ReplyTopostasaguest,yourcommentisunpublished.kellytte·2yearsago HiAhmed, SupposingthefirstdatainCellA1,andtheseconddatainCellA2,youcanapplytheformulaas=IF(A1="",IF(A2="","NODATA",A2),A1) ReplyTopostasaguest,[email protected]·3yearsago IF(D15>30,"OrganicSoils",IF(AND(D15>25,D15<29.99),"ClayeySoil(fine)",IF(AND(D15>20,D15<24.99),"LoamySoil(moderatelyfine)",IF(AND(D15>10,D15<19.99),"LoamySoil(medium)",IF(AND(D15>5,D15<9.99),"LoamySoil(moderatelycoarse)",IF(D15<4.99,"SandySoil(coarse)"))))))Canyoufindtheerror?ReplyTopostasaguest,yourcommentisunpublished.yourname·3yearsago A,b,c,d,e,f,g,a,yReplyTopostasaguest,yourcommentisunpublished.dinesh·3yearsago Name Amount ProfitRange A10,000 B50,000 C42,500 D75,000 E85,000 F23,500 G16,000 I57,000 J81,000 ProfitRangeName Lessthan10,000A 10,000-20,000B 20,000-30,000C 30,0000-50,000D 50,0000-75,000E 75,000-1,00,000F Writeprofitrangein1sttable. anyPleasesayhowtowriteformulaforthisReplyTopostasaguest,yourcommentisunpublished.mgkramarao1·3yearsago irequiredhowtosteelcalculationasperdia(8,10,12,16,20,25,32)andcuttinglengthnoofbarsandnumbersetc wiseinexcelformatReplyTopostasaguest,yourcommentisunpublished.arunima·3yearsago howtouseifconditionforconditionlikeifvalueofxlyingin60-80ReplyTopostasaguest,[email protected]·3yearsago Helloall, IamtryingtobuildanIFstatementtohelpwithourServiceLevelcalculations.WeneedoneQueueNametohaveadifferenttimerequirementthanalloftheotherqueues.IamtryingtofigureouthowtogettheformulatocountallQueueNames(ColumnB)toshowtheyarewithinourServiceLevelAgreement(SLA)iftheyareinthequeueforlessthan30seconds.However,fortheQueuenamedTS_DX_CP_ES*,IneedittoshowitiswithinSLAiflessthan90seconds. Icurrentlyhavethefollowingformulainplaceanditworksforthe90secondrulebutifItrytonexttheIFformulaandchangetheQueueNameandTimeRequirement,Igetanerror. Formulathatworkscurrently=IF(AND([@[QueueName]]="TS_DX_CP_ES*",[@[Queuetime]]<0.00104166666666667),1,0) Formulathatgivesa#VALUE!Error-=IF(AND([@[QueueName]]="TS_DX_CP_ES*",[@[Queuetime]]<0.00104166666666667),1,0),IF(AND([@[QueueName]]="TS_PLU_US*",[@[Queuetime]]<0.000358796296296296),1,0)ReplyTopostasaguest,[email protected]·3yearsago Helloall, IamtryingtobuildanIFstatementtohelpwithourServiceLevelcalculations.WeneedoneQueueNametohaveadifferenttimerequirementthanalloftheotherqueues.IamtryingtofigureouthowtogettheformulatocountallQueueNames(ColumnB)toshowtheyarewithinourServiceLevelAgreement(SLA)iftheyareinthequeueforlessthan30seconds.However,fortheQueuenamedTS_DX_CP_ES*,IneedittoshowitiswithinSLAiflessthan90seconds. Icurrentlyhavethefollowingformulainplaceanditworksforthe90secondrulebutifItrytonexttheIFformulaandchangetheQueueNameandTimeRequirement,Igetanerror. Formulathatworkscurrently=IF(AND([@[QueueName]]="TS_DX_CP_ES*",[@[Queuetime]]<0.00104166666666667),1,0) Formulathatgivesa#VALUE!Error-=IF(AND([@[QueueName]]="TS_DX_CP_ES*",[@[Queuetime]]<0.00104166666666667),1,0),IF(AND([@[QueueName]]="TS_PLU_US*",[@[Queuetime]]<0.000358796296296296),1,0) Anyadviceorhelp?ReplyTopostasaguest,yourcommentisunpublished.PinakiBhattacharyya·4yearsago Hi, Ihaveaproblem.Pleasehelpme. Inarow,3columnswithdifferentvaluemustshowadifferentinterpretationinthetargetcell. =IF(A3>0,"Welcome",IF(B3>0,"Win",IF(C3>0,"Will","HI"))) ThanksPinakiReplyTopostasaguest,yourcommentisunpublished.kelly001·4yearsago HiPinaki, Inyourformula,IfA3>0,bothB3andC3alsomightbegreaterthan0.Similarly,whenB3>0,bothA3andC3alsomightbegreaterthan0. Normally,intheIffunction,youcanset=IF(A3>0,"Welcome",””),whichmeanswhenA3>0,returns“welcome”,whilewhenA3isnotlessthan0,returnsnothing.Inthisfunction,thetwoconditionA3>0andA3<=0areexclusive. Inmyexample=IF(B2<=20,"Unordinary",IF(B2<=80,"Low",IF(B2<=160,"Middle",IF(B2<=200,"High","")))),ThethirdconditionB3<=160includesthesecondconditionB2<=80,andthesecondcontainsthefirsttoo.ReplyTopostasaguest,yourcommentisunpublished.Cab·4yearsago ColumnAhasNumericalValues.ColumnC&Dhavetext. If2ormoreseparateNumericalValuesinanyrowofColumnAmatchthenevaluatethoseRowsinColumnC&DforanyTextthatdoesn'tmatchagivenTextandcountas1.IfTextdoesmatchthenavalueof0isassigned. Soif..A1:A2=1 A3:A4=2 C1=Dog C2=Cat C3=Dog C4:Horse D1=Cat D2=Dog D3=Horse D4=Dog SinceA1&A2matcheachother,evaluateC1,C2,D1,D2andifanycellsdonotmatcheither"Dog"or"Cat"assignavalueof1.Inthisexample,sinceallcellscontaineither"Dog"or"Cat"avalueof0wouldbeassigned. Furthermore, SinceA3&A4matcheachother,evaluateC3,C4,D3,D4andifanycellsdonotmatcheither"Dog"or"Cat"assignavalueof1.Inthisexample,sincecellsC4&D3containsomethingotherthan"Dog"or"Cat"avalueof1wouldbeassigned. Thankyouinadvance!ReplyTopostasaguest,[email protected]·4yearsago HiCab, ToknowmoreabouttheIFfunction,Iguessthiswebpageishelpful. https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2ReplyTopostasaguest,yourcommentisunpublished.jessjess·4yearsago Hello, Whatwouldtheequationforthefollowingbe? IncellK2enteraformulausingtheIFandORfunctions,aswellasstructuredreferences,todetermineifAdamMoriartycanbeagroupleader. a. TheIFfunctionshouldfirstdetermineifthestaffmember’sServiceYearsisgreaterthan3ORifthestaffmember’scollegegraduatestatusis“Yes”.RemembertouseastructuredreferencetotheServiceYearsandtheCollegeGraduatecolumns. b. ThefunctionshouldreturnthetextYesifastaffmembermeetsoneorbothofthosecriteria. c. ThefunctionshouldreturnthetextNoifastaffmembermeetsneitherofthosecriteria.ReplyTopostasaguest,[email protected]·4yearsago Hijessjess, Thankyouforyourcomment.IamsorryIcan’tfigureoutaproperformulatomeetingyourconditions.ToknowmoreabouttheIFfunction,Iguessthiswebpageishelpful. https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2ReplyTopostasaguest,yourcommentisunpublished.MUHAMMADHARIS·4yearsago iwannaknowonethingthatimakeatableofusernameandpasswordinexcelandthereisathirdpartofthemisstatusiwannaknowthatifiuseaformulainathirdcolumnofstatususingmultiple"if"and"or"forthecolumnusernamewhereicanusebothofthemusernameorphonenothenienteredpasswordanditgivesinacolumnofstatusloginifusernameorphonenooneofthemisusedwithpasswordifsomeoneentersymbolsinusernamestatusfailed.ifsomeonecananswermethisisforemailvalidationinexcelthanksifansweredReplyTopostasaguest,[email protected]·4yearsago HiHaris, Couldyousendascreenshotaboutyourproblem? ReplyTopostasaguest,yourcommentisunpublished.Kiran·4yearsago III II II II II II II II HowcanImakeaformulainexcel-IfanyonevalueofthecolumnisIIItheresultshallbedisplayedasIII,ifanyvalueofthecolumnisIIthentheresultshallbedisplayedasIIifIIandIIInotpresentitshallbedisplayedasIReplyTopostasaguest,yourcommentisunpublished.G·4yearsago HowwouldIwritethisintoonefunction? IfD26andE26areblank,thenC26-L7 IfD26isnotblankandE26is,thenD26-L7 IfE26isnotblank,thenE26-L7ReplyTopostasaguest,[email protected]·4yearsago =IF(AND(D26="",E26=""),(C26-L7),IF(AND(D26<>"",E26=""),D26-L7,E26-L7))ReplyTopostasaguest,yourcommentisunpublished.Michelle·4yearsago Iamtryingtowriteaformulatolookat 1.Isthefigureeitherover£1000,orunder-£1000 2.Plusisthenextcellover55%ormore sofarIhave=OR(M4>=1000,M4<=-1000)whichpullsintheoverorunder£1000plusorminusbutamunsurehowtoaddthesecondcriteriapresumeitsanAND(N2>=55%)butwhenIaddittotheformulaitdoesntwork.ReplyTopostasaguest,[email protected]·4yearsago HiMichelle, Loadingascreenshotcanhelpusunderstandyourproblemandneedmucheasier! ReplyTopostasaguest,yourcommentisunpublished.KJ·4yearsago Hi.HowcanIwritethisasformula? Ifyearisequaltocurrentyear,add1. ifyearispreviousyear,add1ORstart1. ifyearis2yearsprior,add1ORstart1.ReplyTopostasaguest,yourcommentisunpublished.FG87·4yearsago HiEveryone,Ineedtowriteaformulaforthefollowing: IFA1="BOTTOMS"ANDA9<14,then14,otherwiseA9 ORA1="TOPS"ANDA9<5,then5,otherwiseA9ReplyTopostasaguest,[email protected]·4yearsago HiFG， Youcan’tcreateaformulatomeetingoneofbothyourconditionsinExcel. ReplyTopostasaguest,yourcommentisunpublished.FG87·4yearsago HiKelly! Iactuallyfiguredoutaworkaround! =ROUND((IFERROR((IF(OR(AND($I9="BOTTOMS",BL9>=$BY$3),AND($I9="TOPS",BL9>=$BY$4)),BL9,VLOOKUP($I9,$BX$3:$BY$4,2,FALSE))),0)),0) I9-populates"BOTTOMS"or"TOPS"dependantonmerchandisecodeentered BL9-minimumsetdefinedbystoreindice BY9-minimumsetdefinedbybuyer(i.ethe14unitsforbottomandandthe5unitsfortops) BX$3:$BY$4-vlookuptominimumsetdefinedbybuyerififstatementisfalseReplyTopostasaguest,yourcommentisunpublished.KimDetera·4yearsago Hello!Iamcurrentlycreatingatemplatethatwillprovidemeavaluedependingonthescoreofthetrainee. IwashopingIcanaddavalueof5foranyteammemberwhogetsacertainquiz% Here'swhatI'mthinking. 5=100% 4=99.9%to90% 3=90%to85% 2=85%to80% 1=<79.99 IhopeyouguyscanhelpmewiththeformulaReplyTopostasaguest,yourcommentisunpublished.Dan·4yearsago Hello,IhaveaprettycomplexformulaIamtryingtowriteanditiskillingmetoadmitdefeatonthisone.SofarIhave: =IF(AND(B2="Prudential",OR(C2={"10YearTerm","15YearTerm","20YearTerm","30YearTerm"})),D2*0.75,(IF(C2="UL",D2*0.8,"0"))) anditworksjustfine.However,Ineedtoaddabout37morecompanieswiththeirowncommissionstructurestoit.IamprettysureIjustneedonemonsterORstatementwithabunchofnestedIFsthrowninIjustamatmywitsendonfiguringoutexactlywheretoplacethem.Anyhelpwouldbeamazing.Thankyou!ReplyTopostasaguest,yourcommentisunpublished.AnhNguyen·4yearsago HiDan, Yourquestionisstillunclear.youmighttoexplainmorebyprovidingasolutionforatleast2companies.However,justhelptoshortenyourORfunctionabit.OR(C2={"10","15","20","30"}&"YearTerm"). ReplyTopostasaguest,yourcommentisunpublished.LINCY·4yearsago =IF(OR((I2=(T2+U2),(I2=(U2+V2)),"Closed","OPEN") HOWTOMAKETHISRIGHT? ReplyTopostasaguest,[email protected]·4yearsago =IF(OR(I2=T2+U2,I2=U2+V2),"CLOSED","OPEN")ReplyTopostasaguest,yourcommentisunpublished.MirkoTipka·4yearsago ImtryingtodoIF(OR(A1=1;AND(A2=1;A3=1)))ReplyTopostasaguest,[email protected]·4yearsago HiMirko, Thankyouforyourcomment.Whatcalculationresultdoyouwanttoget?Couldyoutellmemoreaboutyourproblem? ReplyTopostasaguest,yourcommentisunpublished.DanKinnefer·4yearsago I'mtryingtomaketheformulareturn4possibleanswerwhichis(R-regular,A-absent,L-late,OT-overtime).Mybasisforregularisifthevalueofthecellis"8".SothisistheformulathatImadesofar=IF(H16>7.9,"OT",IF(H16<7.9,"L",IF(H16=8,"R","A"))).Itonlyreturns(OTandL)somethingisverywrongandican'tfigureitout.I'mjustnewtoexcelsopleasehelpmeReplyTopostasaguest,[email protected]·4yearsago HiDan, Trythisformula=IF(H16=8,"R",IF(H16<7.9,"OT","L")) ReplyTopostasaguest,yourcommentisunpublished.DanKinnefer·4yearsago I'mtryingtomaketheformulareturn4possibleanswerwhichis(R-regular,A-absent,L-late,OT-overtime).Mybasisforregularisifthevalueofthecellis"8".SothisistheformulathatImadesofar=IF(H16>7.9,"OT",IF(H16<7.9,"L",IF(H16=8,"R","A"))).Itonlyreturns(OTandL)somethingisverywrongandican'tfigureitout.I'mjustnewtoexcelsopleasehelpme.ReplyTopostasaguest,yourcommentisunpublished.AnhNguyen·4yearsago Trythis:=IF(OR(H16=0,H16=""),"A",IF(H16=8,"R",IF(H16<7.9,"L","OT")))ReplyTopostasaguest,yourcommentisunpublished.pramod·4yearsago howtodeterminetheageofthecell?isthisformularight=IF(I2<30,"futurepayment",AND(IF(I2>30<=45,"30-45days",AND(IF(I2>45<=60,"45-60days",AND(IF(I2>60,"61+days ",0))))))) ReplyTopostasaguest,[email protected]·4yearsago Hipramod, YoucannotuseI2>30<=45informuladirectly.Ihavechangedtheformulaforyou:=IF(I2<=30,"FUTUREPAYMENT",IF(B2<=45,"30-45DAYS",IF(B2<=45,"45-60DAYS",IF(B2<=60,"60+DAYS","")))) ReplyTopostasaguest,yourcommentisunpublished.RamanV·4yearsago Ihavechargescalculationforeg.chargesamountis0thennextcolumnshouldbe0.ifchargesismorethan1andlessthan250thennextcolumnshouldbe250.Ifchargesismorethan250thenactualchargesshouldcomeinnextcolumn(252)ReplyTopostasaguest,yourcommentisunpublished.marhsall·5yearsago howdoimakethisformulastalktooneanother:=IF(OR(AND(MID(K2,6,1)="N",(MID(K2,6,1)="C"),(MID(K2,6,1)="H"),(MID(K2,6,1)="I"),(MID(K2,6,1)="B"),(MID(K2,6,1)="F"),(MID(K2,6,1)="L"),(MID(K2,6,1)="M"),(MID(K2,6,1)="P"),(MID(K2,6,1)="R"),(MID(K2,6,1)="P"),ISTEXT(G2)="61"),OR(AND(RIGHT(K2,2)=G2))),"Good","Review")=IF(AND(RIGHT(K2,2)=G2),"Good","Review")or=IF(NOT(OR(AND(MID(K2,6,1)="N",(MID(K2,6,1)="C"),(MID(K2,6,1)="H"),(MID(K2,6,1)="I"),(MID(K2,6,1)="B"),(MID(K2,6,1)="F"),(MID(K2,6,1)="L"),(MID(K2,6,1)="M"),(MID(K2,6,1)="P"),(MID(K2,6,1)="R"),(MID(K2,6,1)="P"),ISTEXT(G2)="61"),(RIGHT(K2,2)=G2))),"Good","Review")Reply商品 OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook下載OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook如何安裝或卸載最終用戶許可協議購買 OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook資源 Excel的技巧和竅門Word的技巧和竅門Outlook的技巧和竅門Excel函數Excel公式Excel圖表Outlook教程聯絡我們在線教程 OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook新聞和更新 OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlook搜尋搜索更多關於我們我們的團隊用戶感言我們的客戶說得到幫助？ 檢索許可證丟失許可證？報告錯誤錯誤報告論壇在論壇中發帖聯絡我們通過電子郵件與我們聯繫語言 简体中文繁體中文FrançaisDeutschPortuguêsItalianoČeština‎DanskNederlandsالعربيةՀայերենΕλληνικάMagyarBahasaIndonesiaGaelige日本語한국어PolskiRomânăРусскийSlovenščinaEspañolSvenskaไทยTürkçeУкраїнськаTiếngViệtCymraeg