search odd rows only in given cell range

[gtd526]

Hello,
Im using the following formula and wondering how to search odd rows only in the given range (H4:H33)?

LOOKUP(2, 1/((COUNTIF($AI$116:$AI116,$H$4:$H$33)=0)*($AW$4:$AW$33<=$AW$1)),$H$4:$H$33)

Criteria = ($AW$4:$AW$33<=$AW$1), can be all rows
Starting Point when filling down ($AI$116:$AI116)

Thank you

 

[Peter_SSs]

Im currently using:
IF($U117="","",COUNTIF($H$4:$H$33,$U117))
but it count in all rows. i only want to count in ODD rows.

That COUNTIF seems unrelated to the one in the previous formula but instead of
COUNTIF($H$4:$H$33,$U117)
try
SUMPRODUCT(ISODD(ROW($H$4:$H$33))*($H$4:$H$33=U117))

 

[gtd526]

That COUNTIF seems unrelated to the one in the previous formula but instead of
COUNTIF($H$4:$H$33,$U117)
try
SUMPRODUCT(ISODD(ROW($H$4:$H$33))*($H$4:$H$33=U117))

I realize the formula's seem offbeat, but I'm tracking the NBA teams for wagering and I may have a found a slight edge.
These are good suggestions for betting.
the SUMPRODUCT formula seems to work, but it still lists 1 if U118 is blank? see (T:T). T117 is correct.

NBA.xlsm
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
115			Indicator(s)	Indicator >= 70%, Counts(AH:AH) >= 10
116	W/L	Team	Visable			%	$	Cr2 Count	Odd Ball
117	W	CHI	1	Rank2, AL & AM >60%		76.9%	$61.00	26	W	1	88%	MAX
118			1						L	0	50%	MIN
119			1						Psh	0
120			1						Previous		65.4%
121									W	42.5	$80.88	Max $$
122	Above Works!!								L	22.5	Count
123									Total		65
124	Bet on Above								W	43.5
125	If Count is 2, Indicaors MUST Be Different to List Team??????								L	22.5
126	How can I list teams if same indicator for both?????
Favs

Cell Formulas
Range	Formula
R117:R120	R117	=IFERROR(INDEX($C$4:$C$33,MATCH($S117,$A$4:$A$33,0)),"")
S117:S120	S117	=IF($U117="","",INDEX($A$4:$A$33,MATCH($U117,$H$4:$H$33,0)))
T117:T120	T117	=SUMPRODUCT(ISODD(ROW($H$4:$H$33))*($AS$4:$AS$33>=0.7)*($AP$4:$AP$33>=10)*($AW$4:$AW$33<=$AW$1)*($H$4:$H$33=U117))
U117:U120	U117	=IFERROR(LOOKUP(2,1/((COUNTIF($U$116:$U116,$H$4:$H$33)=0)*($AS$4:$AS$33>=0.7)*($AP$4:$AP$33>=10)*($AW$4:$AW$33<=$AW$1)/ISODD(ROW(H$4:H$33))),$H$4:$H$33),"")
W117:W120	W117	=INDEX($AS$4:$AS$33,MATCH($U117,$H$4:$H$33,0))
X117:X120	X117	=IF($U117="","",OFFSET(INDIRECT(ADDRESS(AGGREGATE(15,6,ROW($K$39:$AH$80)/($K$39:$AH$80=$U117),1),AGGREGATE(15,6,COLUMN($K$39:$AH$80)/($K$39:$AH$80=$U117),1))),5,2))
Y117:Y120	Y117	=IF($U117="","",OFFSET(INDIRECT(ADDRESS(AGGREGATE(15,6,ROW($K$39:$AH$80)/($K$39:$AH$80=$U117),1),AGGREGATE(15,6,COLUMN($K$39:$AH$80)/($K$39:$AH$80=$U117),1))),9,2))
AB117	AB117	=IFERROR(IF($AB$120=0,0,MAX($AB$120,$AB$117)),"")
AB118	AB118	=IFERROR(IF($AB$118=0,$AB$120,MEDIAN($AB$118,$AB$120,0)),"")
AA117	AA117	=COUNTIFS($C$4:$C$33,"W",$AS$4:$AS$33,">=.7",$AP$4:$AP$33,">=10",$AP$4:$AP$33,"<>""",$AW$4:$AW$33,"<="&$AW$1)+(AA119*0.5)
AA118	AA118	=COUNTIFS($C$4:$C$33,"L",$AS$4:$AS$33,">=.6",$AP$4:$AP$33,">=10",$AP$4:$AP$33,"<>""",$AW$4:$AW$33,"<="&$AW$1)+(AA119*0.5)
AA119	AA119	=COUNTIFS($C$4:$C$33,"Psh",$AS$4:$AS$33,">=.6",$AP$4:$AP$33,">=10",$AP$4:$AP$33,"<>""",$AW$4:$AW$33,"<="&$AW$1)
AB120	AB120	=IFERROR(AA121/(AA121+AA122),"")
AC120	AC120	=IF(AB120=A47,"Max %%","")
AB121	AB121	=($AA$121*4.55)-($AA$122*5)
AC121	AC121	=IF(AB121>=A38,"Max $$","")
AB123	AB123	=AA121+AA122
AA124:AA125	AA124	=$AA117+$AA121
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

I can't really tell anything from that since I cannot see the relevant data that the column T formulas are referring to. I also do not know what results would be expected in those column T cells.

Here is a smaller, simpler example of the SUMPRODUCT formula counting values in odd rows only. I coloured the odd rows manually to make visual checking easier.
Isn't this what you want the count to do?

24 01 31.xlsm
	H	I	J	K
3			Value	Count in odd rows only
4	a		a	0
5	b		b	2
6	s		c	2
7	d		d	1
8	a		s	2
9	s
10	d
11	c
12	a
13	b
14	d
15	c
16	a
17	s
18
Search Odd rows (3)

Cell Formulas
Range	Formula
K4:K8	K4	=SUMPRODUCT(ISODD(ROW($H$4:$H$17))*($H$4:$H$17=J4))

 

[gtd526]

I can't really tell anything from that since I cannot see the relevant data that the column T formulas are referring to. I also do not know what results would be expected in those column T cells.

Here is a smaller, simpler example of the SUMPRODUCT formula counting values in odd rows only. I coloured the odd rows manually to make visual checking easier.
Isn't this what you want the count to do?

24 01 31.xlsm
	H	I	J	K
3			Value	Count in odd rows only
4	a		a	0
5	b		b	2
6	s		c	2
7	d		d	1
8	a		s	2
9	s
10	d
11	c
12	a
13	b
14	d
15	c
16	a
17	s
18
Search Odd rows (3)

Cell Formulas
Range	Formula
K4:K8	K4	=SUMPRODUCT(ISODD(ROW($H$4:$H$17))*($H$4:$H$17=J4))

Yes it is. Thank you for the help.

 

[Peter_SSs]

Yes it is. Thank you for the help.

You're welcome. So if you are still having trouble with your data & want more help I think we would need some sample data where we had all the inputs and told what the results should be and where they should be. With XL2BB you can hide irrelevant rows and columns and/or make a few smaller mini sheets with sections of data in each.

 

[gtd526]

You're welcome. So if you are still having trouble with your data & want more help I think we would need some sample data where we had all the inputs and told what the results should be and where they should be. With XL2BB you can hide irrelevant rows and columns and/or make a few smaller mini sheets with sections of data in each.

I will attempt to "hide" irrelevant rows and columns for a smaller worksheet. I'm not sure how to do it. I have XL2BB.

 

[gtd526]

I can't really tell anything from that since I cannot see the relevant data that the column T formulas are referring to. I also do not know what results would be expected in those column T cells.

Here is a smaller, simpler example of the SUMPRODUCT formula counting values in odd rows only. I coloured the odd rows manually to make visual checking easier.
Isn't this what you want the count to do?

24 01 31.xlsm
	H	I	J	K
3			Value	Count in odd rows only
4	a		a	0
5	b		b	2
6	s		c	2
7	d		d	1
8	a		s	2
9	s
10	d
11	c
12	a
13	b
14	d
15	c
16	a
17	s
18
Search Odd rows (3)

Cell Formulas
Range	Formula
K4:K8	K4	=SUMPRODUCT(ISODD(ROW($H$4:$H$17))*($H$4:$H$17=J4))

Regarding searching Odd rows only, how would the Index, Match formula look?
I'm currently using:
INDEX($A$4:$A$33,MATCH($U117,$H$4:$H$33,0))
thank you

 

[Peter_SSs]

Regarding searching Odd rows only, how would the Index, Match formula look?
I'm currently using:
INDEX($A$4:$A$33,MATCH($U117,$H$4:$H$33,0))

Try

=INDEX($A$4:$A$33,AGGREGATE(15,6,(ROW($H$4:$H$33)-ROW($H$4)+1)/(($H$4:$H$33=$U117)*ISODD(ROW($H$4:$H$33))),1))

 

[gtd526]

Try

=INDEX($A$4:$A$33,AGGREGATE(15,6,(ROW($H$4:$H$33)-ROW($H$4)+1)/(($H$4:$H$33=$U117)*ISODD(ROW($H$4:$H$33))),1))

Perfect. Thank you very much!!!!!

 

[Peter_SSs]

Cheers.