Create List of Cell Values with Criteria

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Looking to List the Teams (V13) with a SPRD >0 (J:J).
The formula (V13) is listing the Teams, but seems to include blank cells between the Teams.
I want it to list Teams same as the 3 previous formulas (<-10) (M13), (<= -5, >=-9.5) (P13), (<= -1, >=-4.5) (S13), with no Blank cells.
Thank you.


My Wagers.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1StatusDate Balance LeagueType Risk To Win My TeamOppSPRD$$$
2$14.91Balance
3L10/22NFLSPRD$ 5.00$ 4.24WASNYG-2-$5.00
4LNFLSPRD$ 5.00$ 4.63MIABAL3-$5.00
5WNFLSPRD$ 5.00$ 4.42JAXNO3$4.63
6NFLSPRD$ 5.00$ 4.55MY BETS (This Season)
7NFLSPRD$ 5.00$ 4.55<=-10<= -5, >=-9.5<= -1, >=-4.5>= 0
8NFLSPRD$ 5.00$ 4.55W:0#DIV/0!W:0#DIV/0!W:00.0%W:150.0%
9NFLParlay$ 5.00$ 8.93L:0L:0L:1L:1
10Parlay0psh:0psh:0psh:0psh:0
11Parlay0List of <=-10 TeamsList of <=-5, >=-9.5 TeamsList of <=-1, >=-4.5 TeamsList of >0 Teams
12Parlay0TeamW/LSPRDTeamW/LSPRDTeamW/LSPRDTeamW/LSPRD
13Parlay0DAL -10PIT -5.5WASL-2MIAL3
14$15.88Balance-$5.37      DET0-4JAXW3
15 12/3NFLSPRD$ 4.99$ 4.42PHISF3-$4.99            
16 NFLSPRD$ 5.00$ 4.63TENIND1-$5.00            
17 NFLSPRD$ 5.00$ 4.85PITARZ-5.5-$5.00            
180NFLSPRD$ 5.00$ 4.63DETNO-4-$5.00         0 0
19 NFLSPRD$ 5.00$ 4.85DALSEA-10-$5.00          0 
20 NFLSPRD$ 5.00$ 4.55              0 
21 NFLParlay$ 5.00$ 8.93           0 
22Parlay              0 
23Parlay             000
24Parlay             PHI 3
25Parlay             TEN 1
26#REF!Balance-$24.99            
Sheet1
Cell Formulas
RangeFormula
O8,X8,U8,R8O8=N8/(N8+N9)
N8,W8,T8,Q8N8=COUNTIF(N13:N26,"W")+(N10*0.5)
N9,W9,T9,Q9N9=COUNTIF(N13:N26,"L")+(N10*0.5)
N10,W10,T10,Q10N10=COUNTIF(N13:N26,"Psh")
M13:M26M13=IFERROR(INDEX($H$3:$H$440,AGGREGATE(15,6,ROW($H$3:$H$440)-ROW($H$2)/($J$3:$J$440<=-10),ROW()-ROW($M$12))),"")
N13:N26N13=IFERROR(INDEX($A$3:$A$440,AGGREGATE(15,6,ROW($H$3:$H$440)-ROW($H$2)/($J$3:$J$440<=-10),ROW()-ROW($N$12))),"")
O13:O26O13=IFERROR(INDEX($J$3:$J$440,AGGREGATE(15,6,ROW($H$3:$H$440)-ROW($H$2)/($J$3:$J$440<=-10),ROW()-ROW($O$12))),"")
P13:P26P13=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-5)/($J$3:$J$347>=-9.5),ROW()-ROW($P$12))),"")
Q13:Q26Q13=IFERROR(INDEX($A$3:$A$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-5)/($J$3:$J$347>=-9.5),ROW()-ROW($Q$12))),"")
R13:R26R13=IFERROR(INDEX($J$3:$J$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-5)/($J$3:$J$347>=-9.5),ROW()-ROW($R$12))),"")
S13:S26S13=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-1)/($J$3:$J$347>=-4.5),ROW()-ROW($S$12))),"")
T13:T26T13=IFERROR(INDEX($A$3:$A$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-1)/($J$3:$J$347>=-4.5),ROW()-ROW($T$12))),"")
U13:U26U13=IFERROR(INDEX($J$3:$J$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-1)/($J$3:$J$347>=-4.5),ROW()-ROW($U$12))),"")
V13V13=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0),ROW()-ROW($V12))),"")
W13:W26W13=IFERROR(INDEX($A$3:$A$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0),ROW()-ROW($W$12))),"")
X13:X26X13=IFERROR(INDEX($J$3:$J$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0),ROW()-ROW($X$12))),"")
V14:V26V14=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0),ROW()-ROW($V$12))),"")
H15:H20H15=IFERROR(LOOKUP(2, 1/((COUNTIF($H$14:$H14, [NFL.xlsm]Favs!$G$4:$G$35)=0)*( [NFL.xlsm]Favs!$G$4:$G$35<>"")*(([NFL.xlsm]Favs!$E$4:$E$35="bmk")+([NFL.xlsm]Favs!$E$4:$E$35="bth"))), [NFL.xlsm]Favs!$G$4:$G$35),"")
I15:I20I15=IFERROR(IF(1=MOD(MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0),2),INDEX([NFL.xlsm]Favs!$A$4:$A$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0)+1),INDEX([NFL.xlsm]Favs!$A$4:$A$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0)-1)),"")
J20,J15:J18J15=IFERROR(INDEX([NFL.xlsm]Favs!$B$4:$B$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,FALSE)),"")
A15:A20A15=IFERROR(INDEX([NFL.xlsm]Favs!$C$4:$C$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0)),"")
A21A21=IF(COUNTIF($H22:$H25,"?*")=0,"",IF(COUNTIF($G22:$G25,"L"),"L",IF(COUNTIF($G22:$G25,"W")=COUNTIF($H22:$H25,"?*"),"W","Pending")))
K15:K20K15=IFERROR(IF($H15="","",IF(OR($A15="",$A15=0),$F15*-1,IF($A15="W",$G15,IF($A15="L",$F15*-1,0)))),"")
K21K21=IFERROR(IF(OR($A21=0,$A21="Pending"),$F21*-1,IF($A21="W",$G21,IF($A21="L",$F21*-1,""))),"")
G22:G25G22=IFERROR(IFNA(INDEX(#REF!,MATCH($H22,#REF!,FALSE)),0),"")
H22:H25H22=IFERROR(LOOKUP(2, 1/((COUNTIF($H21:$H21,[NFL.xlsm]Favs!$G$4:$G$35)=0)*([NFL.xlsm]Favs!$G$4:$G$35<>"")*(([NFL.xlsm]Favs!$E$4:$E$35="pbmk")+([NFL.xlsm]Favs!$E$4:$E$35="pbth"))),[NFL.xlsm]Favs!$G$4:$G$35),"")
I22:I25I22=IFERROR(IF(1=MOD(MATCH($H22,#REF!,0),2),INDEX(#REF!,MATCH($H22,#REF!,0)+1),INDEX(#REF!,MATCH($H22,#REF!,0)-1)),"")
J22:J25J22=IF($H22<>"", "-", "")
C26C26=#REF!+K26
K26K26=SUM(K15:K23)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
I found a solution to the formula.
I added <>"".
My Wagers.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1StatusDate Balance LeagueType Risk To Win My TeamOppSPRD$$$
2$14.91Balance
3L10/22NFLSPRD$ 5.00$ 4.24WASNYG-2-$5.00
4LNFLSPRD$ 5.00$ 4.63MIABAL3-$5.00
5WNFLSPRD$ 5.00$ 4.42JAXNO3$4.63
6NFLSPRD$ 5.00$ 4.55MY BETS (This Season)
7NFLSPRD$ 5.00$ 4.55IND-5<=-10<= -5, >=-9.5<= -1, >=-4.5>= 0
8NFLSPRD$ 5.00$ 4.55W:0#DIV/0!W:0#DIV/0!W:00.0%W:150.0%
9NFLParlay$ 5.00$ 8.93L:0L:0L:1L:1
10Parlay0psh:0psh:0psh:0psh:0
11Parlay0List of <=-10 TeamsList of <=-5, >=-9.5 TeamsList of <=-1, >=-4.5 TeamsList of >0 Teams
12Parlay0TeamW/LSPRDTeamW/LSPRDTeamW/LSPRDTeamW/LSPRD
13Parlay0DAL -10IND -5WASL-2MIAL3
14$15.88Balance-$5.37   PIT -5.5DET0-4JAXW3
15 12/3NFLSPRD$ 4.99$ 4.42PHISF3-$4.99         PHI 3
16 NFLSPRD$ 5.00$ 4.63TENIND1-$5.00         TEN 1
17 NFLSPRD$ 5.00$ 4.85PITARZ-5.5-$5.00            
180NFLSPRD$ 5.00$ 4.63DETNO-4-$5.00            
19 NFLSPRD$ 5.00$ 4.85DALSEA-10-$5.00            
20 NFLSPRD$ 5.00$ 4.55                
21 NFLParlay$ 5.00$ 8.93             
22Parlay                
23Parlay                
24Parlay                
25Parlay                
26#REF!Balance-$24.99            
Sheet1
Cell Formulas
RangeFormula
O8,X8,U8,R8O8=N8/(N8+N9)
N8,W8,T8,Q8N8=COUNTIF(N13:N26,"W")+(N10*0.5)
N9,W9,T9,Q9N9=COUNTIF(N13:N26,"L")+(N10*0.5)
N10,W10,T10,Q10N10=COUNTIF(N13:N26,"Psh")
M13:M26M13=IFERROR(INDEX($H$3:$H$440,AGGREGATE(15,6,ROW($H$3:$H$440)-ROW($H$2)/($J$3:$J$440<=-10),ROW()-ROW($M$12))),"")
N13:N26N13=IFERROR(INDEX($A$3:$A$440,AGGREGATE(15,6,ROW($H$3:$H$440)-ROW($H$2)/($J$3:$J$440<=-10),ROW()-ROW($N$12))),"")
O13:O26O13=IFERROR(INDEX($J$3:$J$440,AGGREGATE(15,6,ROW($H$3:$H$440)-ROW($H$2)/($J$3:$J$440<=-10),ROW()-ROW($O$12))),"")
P13:P26P13=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-5)/($J$3:$J$347>=-9.5),ROW()-ROW($P$12))),"")
Q13:Q26Q13=IFERROR(INDEX($A$3:$A$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-5)/($J$3:$J$347>=-9.5),ROW()-ROW($Q$12))),"")
R13:R26R13=IFERROR(INDEX($J$3:$J$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-5)/($J$3:$J$347>=-9.5),ROW()-ROW($R$12))),"")
S13:S26S13=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-1)/($J$3:$J$347>=-4.5),ROW()-ROW($S$12))),"")
T13:T26T13=IFERROR(INDEX($A$3:$A$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-1)/($J$3:$J$347>=-4.5),ROW()-ROW($T$12))),"")
U13:U26U13=IFERROR(INDEX($J$3:$J$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347<=-1)/($J$3:$J$347>=-4.5),ROW()-ROW($U$12))),"")
V13:V26V13=IFERROR(INDEX($H$3:$H$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0)/($J$3:$J$347<>""),ROW()-ROW($V$12))),"")
W13:W26W13=IFERROR(INDEX($A$3:$A$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0)/($J$3:$J$347<>""),ROW()-ROW($W$12))),"")
X13:X26X13=IFERROR(INDEX($J$3:$J$347,AGGREGATE(15,6,ROW($H$3:$H$347)-ROW($H$2)/($J$3:$J$347>=0)/($J$3:$J$347<>""),ROW()-ROW($X$12))),"")
H15:H20H15=IFERROR(LOOKUP(2, 1/((COUNTIF($H$14:$H14, [NFL.xlsm]Favs!$G$4:$G$35)=0)*( [NFL.xlsm]Favs!$G$4:$G$35<>"")*(([NFL.xlsm]Favs!$E$4:$E$35="bmk")+([NFL.xlsm]Favs!$E$4:$E$35="bth"))), [NFL.xlsm]Favs!$G$4:$G$35),"")
I15:I20I15=IFERROR(IF(1=MOD(MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0),2),INDEX([NFL.xlsm]Favs!$A$4:$A$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0)+1),INDEX([NFL.xlsm]Favs!$A$4:$A$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0)-1)),"")
J20,J15:J18J15=IFERROR(INDEX([NFL.xlsm]Favs!$B$4:$B$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,FALSE)),"")
A15:A20A15=IFERROR(INDEX([NFL.xlsm]Favs!$C$4:$C$35,MATCH($H15,[NFL.xlsm]Favs!$A$4:$A$35,0)),"")
A21A21=IF(COUNTIF($H22:$H25,"?*")=0,"",IF(COUNTIF($G22:$G25,"L"),"L",IF(COUNTIF($G22:$G25,"W")=COUNTIF($H22:$H25,"?*"),"W","Pending")))
K15:K20K15=IFERROR(IF($H15="","",IF(OR($A15="",$A15=0),$F15*-1,IF($A15="W",$G15,IF($A15="L",$F15*-1,0)))),"")
K21K21=IFERROR(IF(OR($A21=0,$A21="Pending"),$F21*-1,IF($A21="W",$G21,IF($A21="L",$F21*-1,""))),"")
G22:G25G22=IFERROR(IFNA(INDEX(#REF!,MATCH($H22,#REF!,FALSE)),0),"")
H22:H25H22=IFERROR(LOOKUP(2, 1/((COUNTIF($H21:$H21,[NFL.xlsm]Favs!$G$4:$G$35)=0)*([NFL.xlsm]Favs!$G$4:$G$35<>"")*(([NFL.xlsm]Favs!$E$4:$E$35="pbmk")+([NFL.xlsm]Favs!$E$4:$E$35="pbth"))),[NFL.xlsm]Favs!$G$4:$G$35),"")
I22:I25I22=IFERROR(IF(1=MOD(MATCH($H22,#REF!,0),2),INDEX(#REF!,MATCH($H22,#REF!,0)+1),INDEX(#REF!,MATCH($H22,#REF!,0)-1)),"")
J22:J25J22=IF($H22<>"", "-", "")
C26C26=#REF!+K26
K26K26=SUM(K15:K23)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question
Count consecutive value
Replies
2
Views
139
gtd526
gtd526
A
  • Question
Fetch heads up stats between 2 teams
Replies
2
Views
126
AYouQueTai
A
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
164
AYouQueTai
A
J
  • Question
sumif function with delayed application
Replies
5
Views
120
jbrown021286
J
M
  • Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
82
macfuller
M

Forum statistics

Threads
1,215,077
Messages
6,122,992
Members
449,094
Latest member
masterms

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top