Countif using less than or greater than criteria

I have a list of data in cells d11:d15. I want to be able to count how many
of the data points fall within a certain numeric range (ie less than 100 but
greater than 50) but I want to be able to reference a specific cell
containing the criteria rather than using '100' or '50' in the formula. In
my worksheet 50 is in cell I2 and 100 is in cell K2.

Ron
Wed Mar 12 12:21:41 CDT 2008

Try this:

=COUNTIF(D11:D15,"<"&K2)-COUNTIF(D11:D15,"<="&I2)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Kim B." <KimB@discussions.microsoft.com> wrote in message
news:C62A49A9-4114-4456-A637-23D64B33171D@microsoft.com...
>I have a list of data in cells d11:d15. I want to be able to count how
>many
> of the data points fall within a certain numeric range (ie less than 100
> but
> greater than 50) but I want to be able to reference a specific cell
> containing the criteria rather than using '100' or '50' in the formula.
> In
> my worksheet 50 is in cell I2 and 100 is in cell K2.

Ron
Wed Mar 12 12:24:55 CDT 2008

Couple other options:

=SUMPRODUCT((D11:D15>I2)*(D11:D15<K2))
or
=SUMPRODUCT(--(D11:D15>I2),--(D11:D15<K2))
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Kim B." <KimB@discussions.microsoft.com> wrote in message
news:C62A49A9-4114-4456-A637-23D64B33171D@microsoft.com...
>I have a list of data in cells d11:d15. I want to be able to count how
>many
> of the data points fall within a certain numeric range (ie less than 100
> but
> greater than 50) but I want to be able to reference a specific cell
> containing the criteria rather than using '100' or '50' in the formula.
> In
> my worksheet 50 is in cell I2 and 100 is in cell K2.

James_Thomlinson
Wed Mar 12 12:30:05 CDT 2008

Try this...

=SUMPRODUCT((D11:D15>=I2) * (D11:D15<=K2))

here is a link to an explanation of sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Kim B." wrote:

> I have a list of data in cells d11:d15. I want to be able to count how many
> of the data points fall within a certain numeric range (ie less than 100 but
> greater than 50) but I want to be able to reference a specific cell
> containing the criteria rather than using '100' or '50' in the formula. In
> my worksheet 50 is in cell I2 and 100 is in cell K2.