Multiplication As Countifs Criteria

[andybason]

I am trying to use a multiplication formula in a Countifs formula. It's not working, just returning 0. Can anyone see what I am doing wrong?

=COUNTIFS(BallByBallTable[inningsNo],1,BallByBallTable[ballRank],$A26,BallByBallTable[currentWkts],0,BallByBallTable[currentRuns],">(B26*5)")

Thanks

 

[Fluff]

Try it like

=COUNTIFS(BallByBallTable[inningsNo],1,BallByBallTable[ballRank],$A26,BallByBallTable[currentWkts],0,BallByBallTable[currentRuns],">"&(B26*5))

 

[andybason]

Thanks Fluff, that works. What I am actually trying to do is say greater than B26*5 and less than B26*6. When I add BallByBallTable[currentRuns],"<"&(B26*6) it goes back to returning 0 again.

Is there some sort of limit? I'm using Excel 2016.

 

[Fluff]

You should be able to do that. What is your current formula?

 

[andybason]

Hi Fluff, this is what I tried:

=COUNTIFS(BallByBallTable[inningsNo],1,BallByBallTable[ballRank],$A26,BallByBallTable[currentWkts],0,BallByBallTable[currentRuns],">"&($B26*5),BallByBallTable[currentRuns],"<"&($B26*5.99))

 

[andybason]

User error... thanks for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[DavidB2022]

I've been using Countifs to count up the number of times a particular UPC comes up relative to a certain Register to crosscheck our store staff accuracy with physical bills. However, our POS system recently updated and now no longer lists each instance of the UPC separately. They've added a quantity column for each UPC for each transaction. Is there a way to multiply the counted UPC occurrence by the quantity listed in that column?

 

[Fluff]

As this is a totally different question, you need to start a new thread. when you do that please provide samples of your data.
Thanks