Countif using less than or greater than criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kim B. - 12 Mar 2008 18:14 GMT

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.

Reply to this Message

Ron Coderre - 12 Mar 2008 18:21 GMT

Try this:

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

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

Regards,

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

>I have a list of data in cells d11:d15. I want to be able to count how
>many
[quoted text clipped - 4 lines]
> In
> my worksheet 50 is in cell I2 and 100 is in cell K2.

Reply to this Message

Ron Coderre - 12 Mar 2008 18:24 GMT

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)

>I have a list of data in cells d11:d15. I want to be able to count how
>many
[quoted text clipped - 4 lines]
> In
> my worksheet 50 is in cell I2 and 100 is in cell K2.

Reply to this Message

Jim Thomlinson - 12 Mar 2008 18:30 GMT

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

Signature

HTH...

Jim Thomlinson

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

Reply to this Message