Counting a range of numbers

  • Thread starter Thread starter Ty Archer
  • Start date Start date
T

Ty Archer

I have a spreadsheet that I want to count how many cells
contain numbers between 20 and 25, and 40 and 45. Therefore
it will tell me if I have 100 cells that contain values
between 20 & 25, and 71 cells that have values between 40 &
45.

Thanks
 
=COUNTIF($A$1:$A$100,">=20")-COUNTIF($A$1:$A$100,">25") and
=COUNTIF($A$1:$A$100,">=40")-COUNTIF($A$1:$A$100,">45")

or

=SUMPRODUCT(($A$1:$A$100>=20)*($A$1:$A$100<=25)) and
=SUMPRODUCT(($A$1:$A$100>=40)*($A$1:$A$100<=45))
 
=SUMPRODUCT((A1:A100>=20)*(A1:A100<=25))

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
=COUNTIF(A1:A100,">20")-COUNTIF(A1:A100,">=25")

change the > and >= depending on if you want to include 20 and 25

=COUNTIF(A1:A100,">=20")-COUNTIF(A1:A100,">25")

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top