Create List of Cell Values with Criteria

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
684
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
  • Solved
Count consecutive value
Replies
5
Views
372
gtd526
gtd526
P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
601
pankajgrover
P
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
60
aurelius33
A
JenniferMurphy
  • Question Question
I could use help with a bit of a complex table addressing problem
Replies
1
Views
213
JenniferMurphy
JenniferMurphy
V
  • Solved
Is it possible to sort table column values and display the header value in the next set of table columns
Replies
7
Views
126
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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