To count how many are in a certain age group

[LEG]

Hi! I'm looking for at forumla to count how many are in a certain age group.
I have a list of people and their ages and would like to know how many are in
the age group f.eks. between the ages of 30 and 39, 40 and 49 and so on

 

[Peo Sjoblom]

=COUNTIF(A2:A500,">=30")-COUNTIF(A2:A500,">39")

do the same for the other age groups

another way

=SUMPRODUCT(--(A2:A500>=30),--(A2:A500<=39))


--


Regards,


Peo Sjoblom

 

[John Bundy]

one way, don't forget the = if it is less than or equal to.
=COUNT(IF(AND(B1:B4<40,B1:B4>30),B1:B4))
this is known as an array formula, when you finish typing it instead of just
hitting enter you need to hit ctl-shift-enter. if you see {} around the
formula you did it right.

 

[Sandy Mann]

Another option:

=SUMPRODUCT(--(FLOOR(H2:H200,10)=30))

etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[LEG]

Hi
Thanks, but "count, if, and" didn't work even if I got {} to make sure I did
it correctly. Probably because I have a Danish version of Excel 03 and
couldn't translate. Something to do with commas and semicolons. I used
"sumproduct" and that worked. But thanks for responding.

 

[LEG]

Thanks, sumproduct worked! My Danish excel03 couldn't figure countif out.

 

[LEG]

Hi - thanks for responding, but as I do know the Danish word for "floor", I
can't figure out what it means when put together with the formula
"sumproduct" and what it should do. And neither does the Danish help program!
But I did use a sumproduct formula which worked.
--
LEG

"Sandy Mann" skrev:

Another option:

=SUMPRODUCT(--(FLOOR(H2:H200,10)=30))

etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[krcowen]

Sandy

SUMPRODUCT is a pretty powerful function and I continue to find new
uses for it; but, it seems like the FREQUENCY function was pretty much
made for your situation. You might want to break out the old English-
Danish dictionary and see if that function will work for you.

Good luck.

Ken
Norfolk, Va

 

[Peo Sjoblom]

Here is the Danish version

=TÆL.HVIS(A2:A500;">=30")-TÆL.HVIS(A2:A500;">39")



--


Regards,


Peo Sjoblom

 

[T. Valko]

A1:A20 = age (as an integer)
C1 = 30
D1 = 39

=INDEX(FREQUENCY(A$1:A$20,C11-{1,0}),2)

--
Biff
Microsoft Excel MVP


Sandy

SUMPRODUCT is a pretty powerful function and I continue to find new
uses for it; but, it seems like the FREQUENCY function was pretty much
made for your situation. You might want to break out the old English-
Danish dictionary and see if that function will work for you.

Good luck.

Ken
Norfolk, Va

 

[T. Valko]

I like that one!

--
Biff
Microsoft Excel MVP

Another option:

=SUMPRODUCT(--(FLOOR(H2:H200,10)=30))

etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk