countif function

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

Hi,

Would need your help with one issue. I want to count the all numbers
between a range. I have used for instance this following formula

=COUNTIF(O$5:O$104;">="&200)

It gives me how many of my cells contain a number over 200. How would
I do if I want to count cells between for instance 100 and 200?

Using some IF and AND operators?

Thank you so much in advance.

best regards,

Mark
 
Mark,

Two ways

=COUNTIF(O$5:O$104;">=200")-COUNTIF(O$5:O$104;">=100")

or

=SUMPRODUCT((O$5:O$104;">=200")*(O$5:O$104;"<100"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry typo in the formulae. Should be

=COUNT(O$5:O$104)-COUNTIF(O$5:O$104,">200")-COUNTIF(O$5:O$104,"<=100")

=SUMPRODUCT((O$5:O$104<=200)*(O$5:O$104>100))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
=COUNTIF(A1:A300,"<200")-COUNTIF(A1:A300,"<100")

use a formula like this
change the upper and lower numbers to get the range you want
Randall
 
Back
Top