Countif using less than or greater than criteria

[Kim B.]

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

Try this:

=COUNTIF(D1115,"<"&K2)-COUNTIF(D1115,"<="&I2)

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

Regards,

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

 

[Ron Coderre]

Couple other options:

=SUMPRODUCT((D1115>I2)*(D1115<K2))
or
=SUMPRODUCT(--(D1115>I2),--(D1115<K2))
--------------------------

Regards,

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

 

[Jim Thomlinson]

Try this...

=SUMPRODUCT((D1115>=I2) * (D1115<=K2))

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