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
--
LEG

=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


"LEG" wrote in message
...
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
--
LEG

one way, don't forget the = if it is less than or equal to.
=COUNT(IF(AND(B1:B4<40,B1:B430),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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"LEG" wrote:

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
--
LEG

Another option:

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

etc.

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"LEG" wrote in message
...
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
--
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


"John Bundy" skrev:

one way, don't forget the = if it is less than or equal to.
=COUNT(IF(AND(B1:B4<40,B1:B430),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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"LEG" wrote:

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
--
LEG

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


"Peo Sjoblom" skrev:

=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


"LEG" wrote in message
...
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
--
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


Replace @mailinator.com with @tiscali.co.uk


"LEG" wrote in message
...
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
--
LEG

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

On May 7, 3:12*pm, LEG wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"LEG" wrote in message
...
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
--
LEG- Hide quoted text -

- Show quoted text -

Here is the Danish version

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



--


Regards,


Peo Sjoblom


"LEG" wrote in message
...
Thanks, sumproduct worked! My Danish excel03 couldn't figure countif out.
--
LEG


"Peo Sjoblom" skrev:

=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


"LEG" wrote in message
...
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
--
LEG

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

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

--
Biff
Microsoft Excel MVP


wrote in message
...
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

On May 7, 3:12 pm, LEG wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"LEG" wrote in message
...
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
--
LEG- Hide quoted text -

- Show quoted text -

I like that one!

--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
Another option:

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

etc.

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"LEG" wrote in message
...
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
--
LEG