I would like to count how many values there are between a certain range.

E.g.

In column B I have the age of individuals. I would like to count the number
of individuals that are between the age of 18 - 30
Can anyone help?

Also is there a simple formula for the calculation of age?

I don't know that this is simple, but here is one way of doing it:

=COUNT(A1:A10)-COUNTIF(A1:A10,"<30")-COUNTIF(A1:A10,"39")

I had a list of numbers in Column A (rows 1 - 10), and I decided I would
look for ages 30 - 39.

The COUNTA part of the formula gives the count of values in that range, I
then subtracted out the values that were less than 30 or greater than 39 with
the countif statements.

There might be a better way of doing that, but I hope this helps.

Rich

"Hardy" wrote:

I would like to count how many values there are between a certain range.

E.g.

In column B I have the age of individuals. I would like to count the number
of individuals that are between the age of 18 - 30
Can anyone help?

Also is there a simple formula for the calculation of age?

Hi Hardy,

Try one of this:

=COUNTIF(B:B,"<=30")-COUNTIF(B:B,"<18")

=SUMPRODUCT((B1:B100=18)*(B1:B100<=30))

Regards,
KL


"Hardy" wrote in message
...
I would like to count how many values there are between a certain range.

E.g.

In column B I have the age of individuals. I would like to count the
number
of individuals that are between the age of 18 - 30
Can anyone help?

Also is there a simple formula for the calculation of age?

Thanks thats really useful

Just to let you know the first part "COUNT(A1:A10)-" is not needed for the
formula to work.
Thanks again



"Poody" wrote:

I don't know that this is simple, but here is one way of doing it:

=COUNT(A1:A10)-COUNTIF(A1:A10,"<30")-COUNTIF(A1:A10,"39")

I had a list of numbers in Column A (rows 1 - 10), and I decided I would
look for ages 30 - 39.

The COUNTA part of the formula gives the count of values in that range, I
then subtracted out the values that were less than 30 or greater than 39 with
the countif statements.

There might be a better way of doing that, but I hope this helps.

Rich

"Hardy" wrote:

I would like to count how many values there are between a certain range.

E.g.

In column B I have the age of individuals. I would like to count the number
of individuals that are between the age of 18 - 30
Can anyone help?

Also is there a simple formula for the calculation of age?

There is a formula that can be used and it is called sumproduct.

Example: =SUMPRODUCT((B1:B50=18)*(B1:B50<=30))

Each row will be tested
(True)*(True)--1
(False)*(True) --0
and all test will then be summarised, giving you the number persons
between 18 and 30.

Hope it helped
Ola Sandström



Note
If you for some reason want to calculate the average age, this is one
way
=SUMPRODUCT((B1:B50=18)*(B1:B50<=30)*(B1:B50))/SUMPRODUCT((B1:B50=18)*(B1:B50<=30))


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=389344

I think the formula also works using just SUM (instead of SUMPRODUCT)

"olasa" wrote:


There is a formula that can be used and it is called sumproduct.

Example: =SUMPRODUCT((B1:B50=18)*(B1:B50<=30))

Each row will be tested
(True)*(True)--1
(False)*(True) --0
and all test will then be summarised, giving you the number persons
between 18 and 30.

Hope it helped
Ola Sandström



Note
If you for some reason want to calculate the average age, this is one
way
=SUMPRODUCT((B1:B50=18)*(B1:B50<=30)*(B1:B50))/SUMPRODUCT((B1:B50=18)*(B1:B50<=30))


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=389344

only if array-entered (Ctrl+Shift+Enter)

KL


"Sanford Lefkowitz" wrote in
message ...
I think the formula also works using just SUM (instead of SUMPRODUCT)

"olasa" wrote:


There is a formula that can be used and it is called sumproduct.

Example: =SUMPRODUCT((B1:B50=18)*(B1:B50<=30))

Each row will be tested
(True)*(True)--1
(False)*(True) --0
and all test will then be summarised, giving you the number persons
between 18 and 30.

Hope it helped
Ola Sandstrom



Note
If you for some reason want to calculate the average age, this is one
way
=SUMPRODUCT((B1:B50=18)*(B1:B50<=30)*(B1:B50))/SUMPRODUCT((B1:B50=18)*(B1:B50<=30))


--
olasa
------------------------------------------------------------------------
olasa's Profile:
http://www.excelforum.com/member.php...o&userid=17760
View this thread:
http://www.excelforum.com/showthread...hreadid=389344