COUNTIF and range?

[Guest]

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?

 

[Guest]

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

 

[KL]

Hi Hardy,

Try one of this:

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

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

Regards,
KL

 

[Guest]

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

 

[olasa]

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

 

[Guest]

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

 

[KL]

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

KL

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