Countif or SumProduct

[gtd526]

Hello,
Getting the wrong count using this formula (G2). Array give me the same result.
Thanks.

NBA.xlsm
	A	B	C	D	E	F	G
1	Team	ATS		>ATS	Favs		Test
2	HOU	9	L				4
3	CLE	-9	W	-4.9	CLE
4	ATL	-8	W	-6.1	ATL
5	ORL	8	L
6	MIA	5.5	W		MIA
7	PHI	-5.5	L	-6.1
8	LAL	1	W
9	DAL	-1	L	-1.1	DAL
10	IND	-1.5	L	0.3
11	MIL	1.5	W		MIL
12	NO	-3.5	L	-9.7	NO
13	OKC	3.5	W
14	CHA	3.5	W		CHA
15	SAN	-3.5	L	-3.6
16	MIN	4.5	W
17	DEN	-4.5	L	-4.2	DEN
18	MEM	-2.5	W	-0.7	MEM
19	POR	2.5	L
20	WAS	-2.5	L	-4.3	WAS
21	SAC	2.5	W
22	LAC	9.5	L
23	UTA	-9.5	W	1.7	UTA
Sheet1

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(($A$2:$A$23=$E$2:$E$23)*($D$2:$D$23>0)*($C$2:$C$23="W"))

 

[Fluff]

Check that the "blank" cells in column D are in fact empty.

 

[Toadstool]

Hi GTD256,

It returns me a 1, which is what I'd expect.

GTD256.xlsx
	A	B	C	D	E	F	G
1	Team	ATS		>ATS	Favs		Test
2	HOU	9	L				1
3	CLE	-9	W	-4.9	CLE
4	ATL	-8	W	-6.1	ATL
5	ORL	8	L
6	MIA	5.5	W		MIA
7	PHI	-5.5	L	-6.1
8	LAL	1	W
9	DAL	-1	L	-1.1	DAL
10	IND	-1.5	L	0.3
11	MIL	1.5	W		MIL
12	NO	-3.5	L	-9.7	NO
13	OKC	3.5	W
14	CHA	3.5	W		CHA
15	SAN	-3.5	L	-3.6
16	MIN	4.5	W
17	DEN	-4.5	L	-4.2	DEN
18	MEM	-2.5	W	-0.7	MEM
19	POR	2.5	L
20	WAS	-2.5	L	-4.3	WAS
21	SAC	2.5	W
22	LAC	9.5	L
23	UTA	-9.5	W	1.7	UTA
Sheet1

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(($A$2:$A$23=$E$2:$E$23)*($D$2:$D$23>0)*($C$2:$C$23="W"))

 

[Marcelo Branco]

Try

=SUMPRODUCT(($A$2:$A$23=$E$2:$E$23)*($D$2:$D$23>0)*ISNUMBER(D$2:D$23)*($C$2:$C$23="W"))

M.

 

[gtd526]

Try

=SUMPRODUCT(($A$2:$A$23=$E$2:$E$23)*($D$2:$D$23>0)*ISNUMBER(D$2:D$23)*($C$2:$C$23="W"))

M.

Thank you.

 

[Marcelo Branco]

You're welcome. Glad to help.

M.