CountIF

[gtd526]

Hello,
Just wondering why its not counting correctly.
I, L, N = Values to Count according to CountIf in AB
AB = Countif

NBA.xlsm
	I			L		N														AB
4	Rank			ATS +/-		Asst-Tovr Ratio														Pick
5	18			17.5		1.111														1
6	15			-10.5		1.136														1
Favs

Cell Formulas
Range	Formula
I5:I6	I5	=VLOOKUP($A5,TeamRankings!$DD:$DE,2,0)
L5:L6	L5	=IFERROR(VLOOKUP(A5,TeamRankings!$CX$3:$DC$33,6,0),"")
N5	N5	=IFERROR(VLOOKUP(A5,TeamRankings!AT3:AW32,4,0),"")
AB5	AB5	=COUNTIF(I5,"<"&I6)+COUNTIF(L5,">"&L6)+COUNTIF(N5,">"&N6)
N6	N6	=IFERROR(VLOOKUP(A6,TeamRankings!AT3:AW32,4,0),"")
AB6	AB6	=COUNTIF(I6,"<"&I5)++COUNTIF(L6,">"&L5)+COUNTIF(N6,">"&N5)

 

[Saurabhj]

I checked the same formula in excel and didn't find any issue. Result is 1 and 2.
I am checking in Excel 2019.

 

[gtd526]

Hello,
Just wondering why its not counting correctly.
I, L, N = Values to Count according to CountIf in AB
AB = Countif
in groups of two, rows: 5 & 6, 7 & 8, 9 & 10, etc.

NBA.xlsm
	I			L		N														AB
4	Rank			ATS +/-		Asst-Tovr Ratio														Pick
5	18			17.5		1.111														1
6	15			-10.5		1.136														1
Favs

Cell Formulas
Range	Formula
I5:I6	I5	=VLOOKUP($A5,TeamRankings!$DD:$DE,2,0)
L5:L6	L5	=IFERROR(VLOOKUP(A5,TeamRankings!$CX$3:$DC$33,6,0),"")
N5	N5	=IFERROR(VLOOKUP(A5,TeamRankings!AT3:AW32,4,0),"")
AB5	AB5	=COUNTIF(I5,"<"&I6)+COUNTIF(L5,">"&L6)+COUNTIF(N5,">"&N6)
N6	N6	=IFERROR(VLOOKUP(A6,TeamRankings!AT3:AW32,4,0),"")
AB6	AB6	=COUNTIF(I6,"<"&I5)++COUNTIF(L6,">"&L5)+COUNTIF(N6,">"&N5)

Here are the others. Don't know if its the (-) sign or vlookup??

NBA.xlsm
	I			L		N														AB
4	Rank			ATS +/-		Asst-Tovr Ratio														Pick
5	18			17.5		1.111														1
6	15			-10.5		1.136														1
7	10			-18.5		1.444														1
8	16			-5.5		1.188														0
9	3			18.5		1.200														1
10	2			5.5		3.286														1
11	7			-3.0		1.417														1
12	13			-9.0		1.158														0
13	12			9		1.375														1
14	8			-9.5		1.867														1
Favs

Cell Formulas
Range	Formula
I5:I6	I5	=VLOOKUP($A5,TeamRankings!$DD:$DE,2,0)
L5:L14	L5	=IFERROR(VLOOKUP(A5,TeamRankings!$CX$3:$DC$33,6,0),"")
N5	N5	=IFERROR(VLOOKUP(A5,TeamRankings!AT3:AW32,4,0),"")
AB5,AB13,AB11,AB9,AB7	AB5	=COUNTIF(I5,"<"&I6)+COUNTIF(L5,">"&L6)+COUNTIF(N5,">"&N6)
N6	N6	=IFERROR(VLOOKUP(A6,TeamRankings!AT3:AW32,4,0),"")
AB6,AB14,AB12,AB10,AB8	AB6	=COUNTIF(I6,"<"&I5)+COUNTIF(L6,">"&L5)+COUNTIF(N6,">"&N5)
I7:I14	I7	=IFERROR(VLOOKUP($A7,TeamRankings!$DD:$DE,2,0),"")
N7	N7	=IFERROR(VLOOKUP(A7,TeamRankings!AT3:AW32,4,0),"")
N8	N8	=IFERROR(VLOOKUP(A8,TeamRankings!AT3:AW32,4,0),"")
N9	N9	=IFERROR(VLOOKUP(A9,TeamRankings!AT3:AW32,4,0),"")
N10	N10	=IFERROR(VLOOKUP(A10,TeamRankings!AT3:AW32,4,0),"")
N11	N11	=IFERROR(VLOOKUP(A11,TeamRankings!AT3:AW32,4,0),"")
N12	N12	=IFERROR(VLOOKUP(A12,TeamRankings!AT3:AW32,4,0),"")
N13	N13	=IFERROR(VLOOKUP(A13,TeamRankings!AT3:AW32,4,0),"")
N14	N14	=IFERROR(VLOOKUP(A14,TeamRankings!AT3:AW32,4,0),"")

 

[Saurabhj]

I didn't make any change but the result is different (see image).

What is the expected result ?

 

[gtd526]

I didn't make any change but the result is different (see image).

What is the expected result ?

results should be "should be".
If you hoover over AD5 it will show the formula.
I'm comparing 2 rows 5 & 6.

NBA.xlsm
	K	L	M	N																AD																											BE
4	Cover %	ATS +/-	Diff	Asst-Tovr Ratio																Pick																											Should be
5	100.0%	18	32	1.043																1																											2
6	0.0%	-14.0	-32	2.455																0																											1
Favs

Cell Formulas
Range	Formula
K5:K6	K5	=IFERROR(VLOOKUP(A5,TeamRankings!$BW$2:$BZ$33,4,0),"")
L5:L6	L5	=IFERROR(VLOOKUP(A5,TeamRankings!$BW$3:$CB$33,6,0),"")
M5	M5	=IFERROR((L5-L6),"")
N5	N5	=IFERROR(VLOOKUP(A5,TeamRankings!S3:V32,4,0),"")
AD5	AD5	=COUNTIF(K5,">"&K6)+COUNTIF(L5,">"&L6)+COUNTIF(N5,">"&N6)
M6	M6	=IFERROR((L6-L5),"")
N6	N6	=IFERROR(VLOOKUP(A6,TeamRankings!S3:V32,4,0),"")
AD6	AD6	=COUNTIF(I6,"<"&I5)+COUNTIF(L6,">"&L5)+COUNTIF(N6,">"&N5)

 

[gtd526]

I checked the same formula in excel and didn't find any issue. Result is 1 and 2.
I am checking in Excel 2019.

I checked the same formula in excel and didn't find any issue. Result is 1 and 2.
I am checking in Excel 2019.

You can use the XL2BB Addin to copy/post ranges so you don't have to type data. The upper left box below Fx allows you to copy to clipboard and paste where you need.
Here is a link for the download/install.
XL2BB - Excel Range to BBCode | MrExcel Message Board

 

[Fluff]

There is nothing wrong with your formula, although you could just use

=(K5>K6)+(L5>L6)+(N5>N6)

Check that you numbers are real numbers & not text

 

[gtd526]

There is nothing wrong with your formula, although you could just use

=(K5>K6)+(L5>L6)+(N5>N6)

Check that you numbers are real numbers & not text

Yes. I do a vlookup to data that is From Web (in Excel). So I change the format (Power Query) from Text to Number and it seems to work.
Thanks for the reply.

 

[gtd526]

I didn't make any change but the result is different (see image).

What is the expected result ?

I think I found a solution...
I do a vlookup to data that is From Web (in Excel). So I change the format (Power Query) from Text to Number and it seems to work.
thanks for the reply.

 

[Fluff]

Glad you sorted it & thanks for the feedback.