COUNTIF and range?

[Hardy]

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?

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

[Poody]

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?

[KL]

Hi Hardy,

Try one of this:

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

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

Regards,
KL


"Hardy" <[email protected]> wrote in message
news:[email protected]...
>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?

[Hardy]

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?

[Sanford Lefkowitz]

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

[KL]

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

KL


"Sanford Lefkowitz" <[email protected]> wrote in
message news:[email protected]...
>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
>>
>>