# Help with rank using countifs?



## cspengel (Jan 1, 2023)

I did a little reading on the subject, but cannot quite find how to do it if my criteria range is more than one column. The formula I have in mind is something like this:

=COUNTIFS(criteria_range,criteria,values,">"&value)+1

Suppose my data contains a list of names on a sheet called "Salary" in column B. This is not the main sheet, but a secondary sheet.

correlationattempt2.xlsmB1Name2Alec Pierce3Ashton Dulin4Austin Ekeler5Cameron Dicker6Chase McLaughlin7DeAndre Carter8Deon Jackson9Donald Parham Jr.10Gerald Everett11Indianapolis Colts12Jelani Woods13Jordan Wilkins14Joshua Kelley15Joshua Palmer16Justin Herbert17Keenan Allen18Los Angeles Chargers19Michael Pittman Jr.20Mike Williams21Nick Foles22Parris Campbell23Zack MossSalary

On the main sheet, called "Worksheet", I have data my macro puts out.

correlationattempt2.xlsmGHIJKLMNOPQRSTUVWXYZAA1QBFLEXFLEXFLEXFLEXComboIDSalaryProjectionProbabilityTargetDepth TotalTARGET PT 9+Ownership L|UCorrelationValueStackStack POSPlayersFilterPlayer1Player22Justin HerbertMichael Pittman Jr.Chase McLaughlinCameron DickerGerald Everett304995600068.325101.28568.74530.05%0.291.220089LACQB,K,TE303Justin HerbertMichael Pittman Jr.Cameron DickerGerald EverettParris Campbell308465550064.295102.42566.91630.41%0.341.158468LACQB,K,TE304Justin HerbertMichael Pittman Jr.Cameron DickerGerald EverettAlec Pierce308475450063.91598.91566.4730.45%0.341.172752LACQB,K,TE305Austin EkelerJustin HerbertChase McLaughlinCameron DickerGerald Everett351195950073.998.1488.6530.06%0.351.242016LACRB,QB,K,TE406Justin HerbertAustin EkelerChase McLaughlinCameron DickerGerald Everett73995950075.47596.52588.6530.06%0.351.268487LACQB,RB,K,TE407Austin EkelerJustin HerbertChase McLaughlinCameron DickerParris Campbell351216000072.24124.784.86640.04%0.041.204LACRB,QB,K308Austin EkelerJustin HerbertChase McLaughlinCameron DickerAlec Pierce351225900071.86121.1984.35740.05%0.041.217966LACRB,QB,K309Justin HerbertAustin EkelerChase McLaughlinCameron DickerParris Campbell74016000073.815123.08584.86640.04%0.041.23025LACQB,RB,K3010Justin HerbertAustin EkelerChase McLaughlinCameron DickerAlec Pierce74025900073.435119.57584.35740.05%0.041.244661LACQB,RB,K3011Austin EkelerJustin HerbertChase McLaughlinGerald EverettParris Campbell351365950070.61124.788.13640.59%0.271.186722LACRB,QB,TE3012Austin EkelerJustin HerbertChase McLaughlinGerald EverettAlec Pierce351375850070.23121.1987.62740.65%0.271.200512LACRB,QB,TE3013Justin HerbertAustin EkelerChase McLaughlinGerald EverettParris Campbell74165950072.185123.08588.13640.59%0.271.213193LACQB,RB,TE3014Justin HerbertAustin EkelerChase McLaughlinGerald EverettAlec Pierce74175850071.805119.57587.62740.65%0.271.227435LACQB,RB,TE3015Austin EkelerJustin HerbertCameron DickerGerald EverettParris Campbell354665900069.8799.2886.77630.51%0.51.184237LACRB,QB,K,TE4016Austin EkelerJustin HerbertCameron DickerGerald EverettAlec Pierce354675800069.4995.7786.26730.56%0.51.198103LACRB,QB,K,TE4017Justin HerbertAustin EkelerCameron DickerGerald EverettParris Campbell77465900071.44597.66586.77630.51%0.51.210932LACQB,RB,K,TE4018Justin HerbertAustin EkelerCameron DickerGerald EverettAlec Pierce77475800071.06594.15586.26730.56%0.51.225258LACQB,RB,K,TE4019Austin EkelerMichael Pittman Jr.Chase McLaughlinCameron DickerGerald Everett628395650063.6106.1381.17530.02%-0.051.125663LACRB,K,TE3020Austin EkelerJustin HerbertCameron DickerParris CampbellAlec Pierce354975850067.83122.3382.52840.41%0.191.159487LACRB,QB,K3021Justin HerbertAustin EkelerCameron DickerParris CampbellAlec Pierce77775850069.405120.71582.52840.41%0.191.18641LACQB,RB,K3022Austin EkelerJustin HerbertGerald EverettParris CampbellAlec Pierce358275800066.2122.3385.79845.36%0.391.141379LACRB,QB,TE3023Justin HerbertAustin EkelerGerald EverettParris CampbellAlec Pierce81075800067.775120.71585.79845.36%0.391.168534LACQB,RB,TE3024Austin EkelerMichael Pittman Jr.Cameron DickerGerald EverettParris Campbell631865600059.57107.2779.34630.19%0.081.06375LACRB,K,TE3025Austin EkelerMichael Pittman Jr.Cameron DickerGerald EverettAlec Pierce631875500059.19103.7678.83730.21%0.081.076181LACRB,K,TE3026Austin EkelerJustin HerbertChase McLaughlinCameron DickerJoshua Kelley351235800071.0698.1474.7630.06%0.041.225172LACRB,QB,K,RB4027Justin HerbertAustin EkelerChase McLaughlinCameron DickerJoshua Kelley74035800072.63596.52574.7630.06%0.041.252327LACQB,RB,K,RB4028Austin EkelerJustin HerbertChase McLaughlinGerald EverettJoshua Kelley351385750069.4398.1477.97630.76%0.271.207478LACRB,QB,TE,RB4029Justin HerbertAustin EkelerChase McLaughlinGerald EverettJoshua Kelley74185750071.00596.52577.97630.76%0.271.234869LACQB,RB,TE,RB4030Austin EkelerJustin HerbertChase McLaughlinParris CampbellJoshua Kelley351685800067.77124.774.23740.55%-0.041.168448LACRB,QB,RB3031Austin EkelerJustin HerbertChase McLaughlinAlec PierceJoshua Kelley351835700067.39121.1973.72840.61%-0.041.18228LACRB,QB,RB3032Justin HerbertAustin EkelerChase McLaughlinParris CampbellJoshua Kelley74485800069.345123.08574.23740.55%-0.041.195603LACQB,RB,RB3033Justin HerbertAustin EkelerChase McLaughlinAlec PierceJoshua Kelley74635700068.965119.57573.72840.61%-0.041.209912LACQB,RB,RB30WorksheetCell FormulasRangeFormulaY2:Y33Y2=IF($AA$2="",COUNTIF(G2:K2,$Z$2),COUNTIF(G2:K2,$Z$2)*COUNTIF(G2:K2,$AA$2))

What I would like to do is rank each row based on Column O for each name on the list. I assume I would have 23 helper columns ( A helper column for each name), unless someone can think of a better solution. The only criteria is that the row must contain the name it is ranking between columns G:K, otherwise it does not rank it at all. Thanks for any assistance!


----------



## cspengel (Jan 1, 2023)

What I am trying to accomplish in the end is having each player in the list have their top 30 probability rows kept and everything else deleted. However, I am trying to break this down. This is what I had in mind:

correlationattempt2.xlsmGHIJKLM2Justin HerbertMichael Pittman Jr.Chase McLaughlinCameron DickerGerald Everett012403Justin HerbertMichael Pittman Jr.Cameron DickerGerald EverettParris Campbell011974Justin HerbertMichael Pittman Jr.Cameron DickerGerald EverettAlec Pierce15245Austin EkelerJustin HerbertChase McLaughlinCameron DickerGerald Everett013876Justin HerbertAustin EkelerChase McLaughlinCameron DickerGerald Everett014547Austin EkelerJustin HerbertChase McLaughlinCameron DickerParris Campbell07878Austin EkelerJustin HerbertChase McLaughlinCameron DickerAlec Pierce14149Justin HerbertAustin EkelerChase McLaughlinCameron DickerParris Campbell084610Justin HerbertAustin EkelerChase McLaughlinCameron DickerAlec Pierce146111Austin EkelerJustin HerbertChase McLaughlinGerald EverettParris Campbell078712Austin EkelerJustin HerbertChase McLaughlinGerald EverettAlec Pierce141413Justin HerbertAustin EkelerChase McLaughlinGerald EverettParris Campbell084614Justin HerbertAustin EkelerChase McLaughlinGerald EverettAlec Pierce146115Austin EkelerJustin HerbertCameron DickerGerald EverettParris Campbell0131916Austin EkelerJustin HerbertCameron DickerGerald EverettAlec Pierce157417Justin HerbertAustin EkelerCameron DickerGerald EverettParris Campbell01402WorksheetCell FormulasRangeFormulaL2:L17L2=COUNTIF(Worksheet!G2:K2,Salary!$B$2)M2:M17M2=COUNTIFS(L:L,L2,Q:Q,">"&Q2)+1

The helper columns in L and M are for just one player. So I would need 46 helper columns to do it this way.. Which is kind of ridiculous. Any ideas?


----------



## cspengel (Jan 1, 2023)

Well, that is kind of what I ended up doing..pain in the you know what.

probattempt1.xlsmGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCA1QBFLEXFLEXFLEXFLEXComboIDSalaryProjectionProbabilityTargetDepth TotalTARGET PT 9+Ownership L|UCorrelationValueStackStack POSPlayersFilterPlayer1Player2112233445566778899101011111212131314141515161617171818191920202121222223232424252526262Justin HerbertMichael Pittman Jr.Chase McLaughlinCameron DickerGerald Everett304995600068.32599.04568.81530.05%0.291.220089LACQB,K,TE300130301699054813321623017100164101572137001402017040167301691015701570010530121415780995015650124001745018210182101821018213Justin HerbertMichael Pittman Jr.Cameron DickerGerald EverettParris Campbell308465550064.295100.58567.1630.41%0.341.158468LACQB,K,TE300125201658053513150119001666015900153313570135101658016320164601519156101017011631570098401533158101697017700177001770017704Justin HerbertMichael Pittman Jr.Cameron DickerGerald EverettAlec Pierce308475450063.91597.20566.49730.45%0.341.172752LACQB,K,TE3015490180305811373013010181701757016641404015180180901773017930167615920110801330158701063016230128501848010193701937019375Austin EkelerJustin HerbertChase McLaughlinCameron DickerGerald Everett351195950073.996.5588.57530.06%0.351.242016LACRB,QB,K,TE400142401833113881390165501847017930169114200155401843018030182401712161301129013660138601095016570130401881019730197301973019736Justin HerbertAustin EkelerChase McLaughlinCameron DickerGerald Everett73995950075.47595.04588.57530.06%0.351.268487LACQB,RB,K,TE400146401888114371415169301904018580174314410161901902018590188101772163001149014310145101146016970136701941020380203802038020387Austin EkelerJustin HerbertChase McLaughlinCameron DickerParris Campbell351216000072.2412284.95640.04%0.041.204LACRB,QB,K30082201131181511421413011250991010290973085401112011060110809661371066206890791060601100141301141011710117101171011718Austin EkelerJustin HerbertChase McLaughlinCameron DickerAlec Pierce351225900071.86118.6284.34740.05%0.041.217966LACRB,QB,K30143401309195511791498012940117801194011150972012890128401276011281459077508570957074501263087001315010135801358013589Justin HerbertAustin EkelerChase McLaughlinCameron DickerParris Campbell74016000073.815120.49584.95640.04%0.041.23025LACQB,RB,K300892012291891116414560122101096011220104909210121201206012030105514090726077508750675011891451012370127601276012760127610Justin HerbertAustin EkelerChase McLaughlinCameron DickerAlec Pierce74025900073.435117.11584.34740.05%0.041.244661LACQB,RB,K3014620135219801187152501334012230123401150010010133101326013170117214860789089909990787013020902013580101403014030140311Austin EkelerJustin HerbertChase McLaughlinGerald EverettParris Campbell351365950070.6112288.25640.59%0.271.186722LACRB,QB,TE3008220113118150103014130112509910102911990854011120110601108096613710662068907910606011001413011410117101171011710117112Austin EkelerJustin HerbertChase McLaughlinGerald EverettAlec Pierce351375850070.23118.6287.64740.65%0.271.200512LACRB,QB,TE301434013091955011801498012940117801194124409720128901284012760112814590775085709570745012630870013150101358013580135813Justin HerbertAustin EkelerChase McLaughlinGerald EverettParris Campbell74165950072.185120.49588.25640.59%0.271.213193LACQB,RB,TE300892012291891011131456012210109601122122809210121201206012030105514090726077508750675011891451012370127601276012760127614Justin HerbertAustin EkelerChase McLaughlinGerald EverettAlec Pierce74175850071.805117.11587.64740.65%0.271.227435LACQB,RB,TE3014620135219800121715250133401223012341254010010133101326013170117214860789089909990787013020902013580101403014030140315Austin EkelerJustin HerbertCameron DickerGerald EverettParris Campbell354665900069.8798.0986.86630.51%0.51.184237LACRB,QB,K,TE4001338017361129613480123701749016820160513830144301741017080172701606157601063012550127601026015851613017830186201862018620186216Austin EkelerJustin HerbertCameron DickerGerald EverettAlec Pierce354675800069.4994.7186.25730.56%0.51.198103LACRB,QB,K,TE4015760190911460142501347019250188301762144801644019250188001902017971646011740145601476011690171301383019660102063020630206317Justin HerbertAustin EkelerCameron DickerGerald EverettParris Campbell77465900071.44596.58586.86630.51%0.51.210932LACQB,RB,K,TE40014080182011372138301303018340177701681141501538018280179001811016961597011130135001370010790164116540186601957019570195701957WorksheetCell FormulasRangeFormulaY2:Y17Y2=IF($AA$2="",COUNTIF(G2:K2,$Z$2),COUNTIF(G2:K2,$Z$2)*COUNTIF(G2:K2,$AA$2))


----------

