Menu
I need a formula to count the number of cells in a range that are between 10 and 10.000:
I have:
=COUNTIF(B2:B292,>10 AND <10.000)
But how do I put the comparison operators in without getting a formula error?
249
Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.
To count numbers or dates that fall within a range (such as greater than 9000 and at the same time less than 22500), you can use the COUNTIFS function.
You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
If you have Excel 2007 or later use COUNTIFS with an "S" on the end, i.e.
=COUNTIFS(B2:B292,">10",B2:B292,"<10000")
You may need to change commas , to semi-colons ;
In earlier versions of excel use SUMPRODUCT like this
=SUMPRODUCT((B2:B292>10)*(B2:B292<10000))
Note: if you want to include exactly 10 change > to >= - similarly with 10000, change < to <=
172
Example-
For cells containing the values between 21-31, the formula is:
=COUNTIF(M$7:M$83,">21")-COUNTIF(M$7:M$83,">31")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
