adding .5 to existing Countif with Criteria

[gtd526]

Hello,
Looking to Count "W" and "L" (Z:Z), then add .5 for each "Psh" listed to each "W","L" (if "Psh" exists) in column (Z:Z), according to the Matching Team(AV:AV).
I've tried a formula (AW296), but its not working. I will copy the new formula to the entire range (AW:AW)
Results should be:
OKC = .5-1.5
MEM = 2.5-.5

Range where the Countif exists:

My Wagers.xlsm
	AU	AV	AW	AX
284		Team	W/L Record	W%
285		CLE	4-6	40%
286		LAL	0-1	0%
287		CLE	4-6	40%
288		HOU	2-1	67%
289		CLE	4-6	40%
290		ORL	1-0	100%
291		CLE	4-6	40%
292		CLE	4-6	40%
293		CLE	4-6	40%
294		HOU	2-1	67%
295		CLE	4-6	40%
296	PSH	OKC	0
297		CLE	4-6	40%
298		MEM	2-0	100%
299		CLE	4-6	40%
300		CLE	4-6	40%
301		HOU	2-1	67%
302	PSH	MEM	2-0	100%
303		OKC	0-1	0%
304		MEM	2-0	100%
305		MEM	2-0	100%
306			0-0
307			0-0
NBA

Cell Formulas
Range	Formula
AV285:AV307	AV285	=IFERROR(INDEX($H$3:$H$768,AGGREGATE(15,6,ROW($H$3:$H$768)-ROW($H$2)/($J$3:$J$768<=-10),ROW()-ROW($AV$284))),"")
AW297:AW307,AW285:AW295	AW285	=COUNTIFS($Y$290:$Y$769,AV285,$Z$290:$Z$769,"W")&"-"&COUNTIFS($Y$290:$Y$769,AV285,$Z$290:$Z$769,"L")
AX285:AX307	AX285	=IFERROR(LEFT(AW285,1)/(LEFT(AW285,1)+RIGHT(AW285,1)),"")
AW296	AW296	=COUNTIFS($Y$290:$Y$769,AV296,$Z$290:$Z$769,"W"+(COUNTIFS($Y$290:$Y$769,AV296,$Z$290:$Z$769,"PSH")/2)&"-"&COUNTIFS($Y$290:$Y$769,AV296,$Z$290:$Z$769,"L")*(COUNTIFS($Y$290:$Y$769,AV296,$Z$290:$Z$769,"PSH")/2))

Here is the Range we are Counting

My Wagers.xlsm
	Y	Z	AA
289	Team	W/L	SPRD
290	CLE	L	-13
291	LAL	L	-11
292	CLE	W	-13
293	HOU	L	-12.5
294	CLE	W	-11.5
295	ORL	W	-10.5
296	CLE	L	-12
297	CLE	W	-16
298	CLE	L	-17
299	HOU	W	-10
300	CLE	W	-11
301	OKC	PSH	-18
302	CLE	L	-14.5
303	MEM	W	-12
304	CLE	L	-15
305	CLE	L	-16
306	HOU	W	-10.5
307	MEM	PSH	-12
308	OKC	L	-13
309	MEM	W	-12
310	MEM		-11.5
311
312
313
NBA

Cell Formulas
Range	Formula
Y290:Y313	Y290	=IFERROR(INDEX($H$3:$H$768,AGGREGATE(15,6,ROW($H$3:$H$768)-ROW($H$2)/($J$3:$J$768<=-10),ROW()-ROW($Y$289))),"")
Z290:Z313	Z290	=IFERROR(INDEX($A$3:$A$768,AGGREGATE(15,6,ROW($H$3:$H$768)-ROW($H$2)/($J$3:$J$768<=-10),ROW()-ROW($Z$289))),"")
AA290:AA313	AA290	=IFERROR(INDEX($J$3:$J$768,AGGREGATE(15,6,ROW($H$3:$H$768)-ROW($H$2)/($J$3:$J$768<=-10),ROW()-ROW($AA$289))),"")

 

[Peter_SSs]

I am not certain about your row offsets but try this in AW289

=COUNTIFS($Y$290:$Y$769,AV285,$Z$290:$Z$769,"W")+0.5*(AU285="PSH")&"-"&COUNTIFS($Y$290:$Y$769,AV285,$Z$290:$Z$769,"L")+0.5*(AU285="PSH")

 

[gtd526]

I am not certain about your row offsets but try this in AW289

=COUNTIFS($Y$290:$Y$769,AV285,$Z$290:$Z$769,"W")+0.5*(AU285="PSH")&"-"&COUNTIFS($Y$290:$Y$769,AV285,$Z$290:$Z$769,"L")+0.5*(AU285="PSH")

I modified your formula, and it works. Thank you.

COUNTIFS($Y$290:$Y$769,AV302,$Z$290:$Z$769,"W")+(COUNTIFS($Y$290:$Y$769,AV302,$Z$290:$Z$769,"PSH")*0.5)&"-"&COUNTIFS($Y$290:$Y$769,AV302,$Z$290:$Z$769,"L")+(COUNTIFS($Y$290:$Y$769,AV302,$Z$290:$Z$769,"PSH")*0.5)

 

[Peter_SSs]

I modified your formula

Ah, yes, I missed this word ..

then add .5 for each "Psh"

Anyway, glad you could adjust.