Hello
I have the formula below and want to replace the ">96" with a cell reference but I can't get it to work. I thought the changes in the second formula would do it but it just returns 0.
What am I doing wrong?
Thank you
Original formula:
=SUM(COUNTIFS(BallByBallTable[ballNumber],">96",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[wides],">0",BallByBallTable[ballNumber],">96",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[noBalls],">0",BallByBallTable[ballNumber],">96",BallByBallTable[ballNumber],"<=120"))
My attempt:
=SUM(COUNTIFS(BallByBallTable[ballNumber],">(120-B5)",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[wides],">0",BallByBallTable[ballNumber],">(120-B5)",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[noBalls],">0",BallByBallTable[ballNumber],">(120-B5)",BallByBallTable[ballNumber],"<=120"))
I have the formula below and want to replace the ">96" with a cell reference but I can't get it to work. I thought the changes in the second formula would do it but it just returns 0.
What am I doing wrong?
Thank you
Original formula:
=SUM(COUNTIFS(BallByBallTable[ballNumber],">96",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[wides],">0",BallByBallTable[ballNumber],">96",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[noBalls],">0",BallByBallTable[ballNumber],">96",BallByBallTable[ballNumber],"<=120"))
My attempt:
=SUM(COUNTIFS(BallByBallTable[ballNumber],">(120-B5)",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[wides],">0",BallByBallTable[ballNumber],">(120-B5)",BallByBallTable[ballNumber],"<=120"))-SUM(COUNTIFS(BallByBallTable[noBalls],">0",BallByBallTable[ballNumber],">(120-B5)",BallByBallTable[ballNumber],"<=120"))