Count age ranges

J

James

I have a column showing the ages of a number of people. I want to count the
number of people who are:

Over 65
60 to 65
and 55 to 60

The countif formula I tried is not working. Thank you in advance for any help.

James
 
T

T. Valko

You have an overlap for people age 60. They'll be counted twice.

Over 65:

=COUNTIF(rng,">65")

60 to 65:

=COUNTIF(rng,">=60")-COUNTIF(rng,">65")

55 to 59:

=COUNTIF(rng,">=55")-COUNTIF(rng,">59")
 
E

excelent

=COUNTIF(A1:A21,">65")
=SUMPRODUCT((A1:A21>60)*(A1:A21<65))
=SUMPRODUCT((A1:A21>=55)*(A1:A21<=60))



"James" skrev:
 
P

Peo Sjoblom

Could you be a bit more specific, what did not work and what was your
formula?

=COUNTIF(A1:A100,">65")

will count all over 65



=COUNTIF(A1:A100,">60")-COUNTIF(A1:A100,">65")

will count from 61 and up to 65 (including 65)


=COUNTIF(A1:A100,">55")-COUNTIF(A1:A100,">60")

will count 56 to 60 (including 60)


You might have to tweak >= > etc depending on the limits for each age group
just make sure you don't double count



--


Regards,


Peo Sjoblom
 
T

Teethless mama

count age over 65
=COUNTIF(rng,">65")

count age from 60 to 65
=SUM(COUNTIF(rng,{">=60",">65"})*{1,-1})

count age from 55 to 60
=SUM(COUNTIF(rng,{">=55",">60"})*{1,-1})
 
P

Peo Sjoblom

That will include 60 twice

=SUM(COUNTIF(rng,{">=55",">=60"})*{1,-1})

will exclude 60

--


Regards,


Peo Sjoblom
 
J

James

Thank you all for helping. The formula below worked for me so am able to
manipulate it for the other ranges. Thanks again!
 
I

ilia

Array-entered in four consequtive cells (such as B1:B4) with Ctrl+Shift
+Enter.

=FREQUENCY($A$1:$A$100,{54,60,65,200})

B1 = 54 and younger, functionally "under 55"
B2 = between 55 and 60, inclusively
B3 = between 60 and 65, inclusively
B4 = between 65 and 200, functionally "over 65"
 
Top