Countifs With Fixed Criterion

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
175
Office Version
  1. 2016
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRS
1inningsNoballNumberballRankbowlerbatsmanrunsbyeslegByeswidesnoBallswicketbowlingStyleLeagueconvDatehowDismissedRankbattingStylecurrentWkts
211149764280490100000Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught1Right-hand bat0
31224976445955400000Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017lbw1Right-hand bat0
41334976445955100000Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017lbw2Right-hand bat0
514449764280490100000Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught2Right-hand bat0
61554976445955100000Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017lbw3Right-hand bat0
715649764280490100100Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught3Right-hand bat0
816749764280490100000Right-arm mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught4Right-hand bat0
917849535280490101000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught5Right-hand bat0
101894953545955000000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017lbw4Right-hand bat0
1119104953545955400000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017lbw5Right-hand bat0
12110114953545955000001Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017lbw6Right-hand bat1
131111249535486672400000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught1Right-hand bat1
141121349535486672000000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught2Right-hand bat1
151131449619280490400000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught6Right-hand bat1
161131549619280490100100Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught7Right-hand bat1
171141649619280490000000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught8Right-hand bat1
181151749619280490600000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught9Right-hand bat1
191161849619280490000001Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught10Right-hand bat2
201171949619486672600000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught3Right-hand bat2
211182049619486672100000Right-arm fast-mediumSri Lanka in South Africa T20I Series 2016/1720/01/2017caught4Right-hand bat2
2211921360456486672100000Right-arm medium-fastSri Lanka in South Africa T20I Series 2016/1720/01/2017caught5Right-hand bat2
2312022360456321777100000Right-arm medium-fastSri Lanka in South Africa T20I Series 2016/1720/01/2017caught1Left-hand bat2
2412123360456486672100000Right-arm medium-fastSri Lanka in South Africa T20I Series 2016/1720/01/2017caught6Right-hand bat2
2512224360456321777100000Right-arm medium-fastSri Lanka in South Africa T20I Series 2016/1720/01/2017caught2Left-hand bat2
2612325360456486672100000Right-arm medium-fastSri Lanka in South Africa T20I Series 2016/1720/01/2017caught7Right-hand bat2
2712426360456321777000000Right-arm medium-fastSri Lanka in South Africa T20I Series 2016/1720/01/2017caught3Left-hand bat2
2812527268740486672400000LegbreakSri Lanka in South Africa T20I Series 2016/1720/01/2017caught8Right-hand bat2
BallByBallBatting
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
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
Excel Formula:
COUNTIFS(BallByBallTable[ballNumber],">66",BallByBallTable[ballNumber],"<97",BallByBallTable[wicket],">0")


hth....
 

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
175
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top