Countifs With Fixed Criterion

[rob51852]

Hi,

I am using the following formula to count the number of "balls" in a table (extract below). I want to amend it so that it only counts balls where currentWkts at ballNumber 66 are 0. Is this possible with a formula?

=COUNTIFS(BallByBallTable[ballNumber],">66",BallByBallTable[ballNumber],"<97")

Thanks

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	inningsNo	ballNumber	ballRank	bowler	batsman	runs	byes	legByes	wides	noBalls	wicket	bowlingStyle	League	convDate	howDismissed	Rank	battingStyle	currentWkts
2	1	1	1	49764	280490	1	0	0	0	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	1	Right-hand bat	0
3	1	2	2	49764	45955	4	0	0	0	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	lbw	1	Right-hand bat	0
4	1	3	3	49764	45955	1	0	0	0	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	lbw	2	Right-hand bat	0
5	1	4	4	49764	280490	1	0	0	0	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	2	Right-hand bat	0
6	1	5	5	49764	45955	1	0	0	0	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	lbw	3	Right-hand bat	0
7	1	5	6	49764	280490	1	0	0	1	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	3	Right-hand bat	0
8	1	6	7	49764	280490	1	0	0	0	0	0	Right-arm medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	4	Right-hand bat	0
9	1	7	8	49535	280490	1	0	1	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	5	Right-hand bat	0
10	1	8	9	49535	45955	0	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	lbw	4	Right-hand bat	0
11	1	9	10	49535	45955	4	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	lbw	5	Right-hand bat	0
12	1	10	11	49535	45955	0	0	0	0	0	1	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	lbw	6	Right-hand bat	1
13	1	11	12	49535	486672	4	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	1	Right-hand bat	1
14	1	12	13	49535	486672	0	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	2	Right-hand bat	1
15	1	13	14	49619	280490	4	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	6	Right-hand bat	1
16	1	13	15	49619	280490	1	0	0	1	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	7	Right-hand bat	1
17	1	14	16	49619	280490	0	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	8	Right-hand bat	1
18	1	15	17	49619	280490	6	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	9	Right-hand bat	1
19	1	16	18	49619	280490	0	0	0	0	0	1	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	10	Right-hand bat	2
20	1	17	19	49619	486672	6	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	3	Right-hand bat	2
21	1	18	20	49619	486672	1	0	0	0	0	0	Right-arm fast-medium	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	4	Right-hand bat	2
22	1	19	21	360456	486672	1	0	0	0	0	0	Right-arm medium-fast	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	5	Right-hand bat	2
23	1	20	22	360456	321777	1	0	0	0	0	0	Right-arm medium-fast	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	1	Left-hand bat	2
24	1	21	23	360456	486672	1	0	0	0	0	0	Right-arm medium-fast	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	6	Right-hand bat	2
25	1	22	24	360456	321777	1	0	0	0	0	0	Right-arm medium-fast	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	2	Left-hand bat	2
26	1	23	25	360456	486672	1	0	0	0	0	0	Right-arm medium-fast	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	7	Right-hand bat	2
27	1	24	26	360456	321777	0	0	0	0	0	0	Right-arm medium-fast	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	3	Left-hand bat	2
28	1	25	27	268740	486672	4	0	0	0	0	0	Legbreak	Sri Lanka in South Africa T20I Series 2016/17	20/01/2017	caught	8	Right-hand bat	2
BallByBallBatting

 

[fadee2]

Your formula only counts balls in between ball number 66 and 97.

=COUNTIFS(BallByBallTable[ballNumber],">66",BallByBallTable[ballNumber],"<97")

you have to add another criteria for wickets column. try

COUNTIFS(BallByBallTable[ballNumber],">66",BallByBallTable[ballNumber],"<97",BallByBallTable[wicket],">0")

hth....

 

[rob51852]

Thanks Fadee. I tried something similar to start with but what I am trying to do is count balls where currentWkts are 0 at ballNumber 66, irrespective of what currentWkts are between ballNumber 67 and 96.