What's Google Sheets alternative to CountIFS that works with ...

https://stackoverflow.com/questions/69589540/whats-google-sheets-alternative-to-countifs-that-works-with-or-logic/69589952#69589952. Home Public Questions Tags Users Collectives ExploreCollectives FindaJob Jobs Companies Teams StackOverflowforTeams –Collaborateandshareknowledgewithaprivategroup. CreateafreeTeam WhatisTeams? Teams CreatefreeTeam CollectivesonStackOverflow Findcentralized,trustedcontentandcollaboratearoundthetechnologiesyouusemost. Learnmore Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. Learnmore What'sGoogleSheetsalternativetoCountIFSthatworkswithORlogic? AskQuestion Asked 1monthago Active 1monthago Viewed 39times 0 Ihavebloodpressuredataintwocolumns(SYS&DIA)andwanttocountthestatesaccordingtotheserules: criticalSYS>180ORDIA>120 highstage2SYS>140ORDIA>90 highstage1SYS130-139ORDIA80-89 elevatedSYS120-129ANDDIA<80 normalSYS<120ANDDIA<80 sothatfor SYSDIA 12073(that'selevated) 12381(highstage1) 11283(highstage1) 12968(elevated) 11872(normal) 11980(elevated) Theresultshouldbe normal:1 elevated:2 highstage1:3 For"elevated": =COUNTIFS(SYS;">=120";SYS;"<=129";DIA;"<80")seemstoworkfine. HowdoIhandle"highstage1"andothercaseswithOR??? I'malsoconsideringworkingaroundtheissuebyaddingathirdcolumn(asshownabove)withadescriptionofstateviasomethinglike: =IFS(OR(ISBETWEEN(SYS;130;139);ISBETWEEN(DIA;80;89));"Highstage1";AND(ISBETWEEN(SYS;120;129);DIA<80);"Elevated")andsoon... andthenIguessIcanjustcountwordsinthatcolumn.Still,oneformulaforallthesestateswillgetkindofmessyandIsupposeI'mmissingacleanersolution. google-sheetsgoogle-sheets-formulacountif Share Improvethisquestion Follow askedOct15at19:09 krzwierz79krzwierz79 2344bronzebadges 4 Itseemstomethatyoushouldonlybeworryingaboutnon-normaldefinitions,witheverythingotherthanthoseconditionsbeingconsidered"normal."Asitis,myowntypicalbloodpressureof120/80(or,say,120/84)fallsnowhereinyourreadingsandwouldreturnanerror(or,withIFERROR,anull).Irecommendthatyouthinkthroughthatandrevisebothyoursheetandthepostaccordingly.Commentbelowif/whenyou'vedonethis,andI'lltakeanotherlook. – ErikTyler Oct15at19:18 Inaddition,youseemtohaveyourcountsoffwithinyourpost,evenasyouhaveit.Isee3"elevated"and2"highstage1,"not2"elevated"and3"highstage1." – ErikTyler Oct15at19:19 TherearerulesmissingforSYSbeing140orDIAbeing90. – kishkin Oct16at9:10 Alsofromyourexample(lastrow)SYS119,DIA80shouldbehighstage1andnotelevatedaccordingtoyourrules. – kishkin Oct16at9:23 Addacomment  |  2Answers 2 Active Oldest Votes 1 see: =INDEX(QUERY(IF(B11:B="",, IF(((A11:A>=120)*(A11:A<=129))*(B11:B<80),A4, IF((A11:A<120)*(B11:B<80),A5, IF((A11:A>180)+(B11:B>120),A1, IF((A11:A>140)+(B11:B>90),A2, IF(((A11:A>=130)*(A11:A<=139))+((B11:B>=80)*(B11:B<=89)),A3)))))), "selectCol1,count(Col1)whereCol1isnotnullgroupbyCol1labelcount(Col1)''")) Share Improvethisanswer Follow answeredOct15at19:50 player0player0 82.6k88goldbadges4141silverbadges8282bronzebadges Addacomment  |  0 Youcanhaveareference(1onthescreenshot)tolookupconditionsbySYSorDIAvalues(presumingonlywholenumbersareused): SYSDIACondition 00normal 12080elevated 13080highstage1 14191highstage2 181121critical Thenusingthisreferenceaformulainonecell(2onthescreenshot): =QUERY( FILTER( VLOOKUP( QUERY( SPLIT( FLATTEN( ROW(A9:A)&"♥"&{IFNA(MATCH(A9:A,H2:H6)),IFNA(MATCH(B9:B,I2:I6))} ), "♥" ), "SELECTMAX(Col2) GROUPBYCol1 LABELMAX(Col2)''", ), {SEQUENCE(ROWS(J2:J6)),J2:J6}, 2 ), A9:A<>"", B9:B<>"" ), "SELECTCol1,COUNT(Col1)GROUPBYCol1LABELCOUNT(Col1)''", ) Ifyouneedtogetconditionforeveryrow(3onthescreenshot)youcanusethisformula: =ARRAYFORMULA( IFS( A9:A="",, B9:B="",, True, VLOOKUP( QUERY( SPLIT( FLATTEN( ROW(A9:A)&"♥"&{IFNA(MATCH(A9:A,H2:H6)),IFNA(MATCH(B9:B,I2:I6))} ), "♥" ), "SELECTMAX(Col2) GROUPBYCol1 LABELMAX(Col2)''", ), {SEQUENCE(ROWS(J2:J6)),J2:J6}, 2 ) ) ) Share Improvethisanswer Follow answeredOct16at9:46 kishkinkishkin 3,94711goldbadge2323silverbadges3535bronzebadges Addacomment  |  YourAnswer ThanksforcontributingananswertoStackOverflow!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy Nottheansweryou'relookingfor?Browseotherquestionstaggedgoogle-sheetsgoogle-sheets-formulacountiforaskyourownquestion. TheOverflowBlog Smashingbugstosetaworldrecord:AWSBugBust Podcast399:ZerotoMVPwithoutprovisioningadatabase FeaturedonMeta Reducingtheweightofourfooter NewresponsiveActivitypage Communityinputneeded:Therulesforcollectivesarticles A/BtestingontheAskpage Visitchat Related 184 Getthelastnon-emptycellinacolumninGoogleSheets 12 GoogleSheetsCOUNTIF/COUNTIFSformulasbasedonmultiplecriteria 0 COUNTIFSwithdifferentdateformatsingooglesheets 0 Multi-columndependentcountifsinGoogleSheets 0 HowtocountcellsthatcontainwordinGoogleSheets? 1 UsingCOUNTIFSinGoogleSheetswithapossibleblankcriteria 0 GoogleSheetsArrayFormulaCountifsnotworkingcorrectly 0 GoogleSheets-COUNTIFSinArrayFormula 0 GoogleSheetshelp-HowtoapplyCOUNTIFSwithdifferentsizecriteriaranges 0 Countifsacrossmultiplesheets HotNetworkQuestions AoCG2021Day10:Memoryreallocationroutine Findingtheresonancefrequencyforforceddampedoscillations Aquestionontableformatting Isthereanysortofnaturalmechanismthatwouldexplainwhymygiantsaretallerorshorterdependingonhowmanyhornstheyhave? cunnilingusvscunnilinctus Photoofspritesinacleardarksky,isthispossible? (Jehovah'sWitnesses)Whatreason(s)istheretoactuallyloveGod? HowtogetthebuildprocedureforaDebianpackage? HowdoIleaveacompanyongoodtermsifmyprojectmanagerviewsleavingthecompanyasaformofbetrayal? Howtogetarangedbonusactionattackbefore/withoutattackingorusingacombatmanoeuvre? ChunkSortaSequence Isthisdruidsubclassbalancedcomparedtothenormaldruidsubclasses? 4-YearOldFightstheSchoolRoutine DoIholdthecopyrightontestanswers? Authorshipofastudentwhopublishedseparatelywithoutpermission What'sthedifferencebetween:'Jenepeuxpasvenir'and'Jepeuxnepasvenir'? Canyoumakecrepes/pancakesonastainlesssteelpanwithoutoilorbuttersothatitdoesn'tstick? HowcanIavoidoverdraftfeeswhilemovingallofmyfundstoanewbank? Howtochangethecolourofasinglelineinagraph What'sthegrammarusedin"justwhatquarterhedidnotnowremember"fromthebook1984? WhyarescientistssayingthattheOmicronCOVID-19variantisareasontogetabooster? WillaptinstallationofPython3.10breakthe3.8version? Howtohooktoasystemfunction RAW,doesdeathcallaffectallcreaturesoronly"foes" morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings