Check to see if cell data is within a range

M

mwrfsu

I want to count the number of cells with in a range that fall between
certain range. I can get it to work with one criteria but not whe
adding the second.

For example, I want to count the number of cells between I8:I24 tha
are >= 75 but < 90.

This works for greater than 75 but not sure what to do for the les
than 90.
=COUNTIF(FIN!$I$8:$I$24,">=.75")

Thank you for any help
 
G

Gary's Student

This technique can be adapted to any combination of conditions.

Consider the formula

=(I8>=75)*(I8<90)

It returns 1 for the numbers you want to count and 0 otherwise. Enter this
in a column to the right of I and copy down. The sum of the 1's is what you
want.
 
D

Duke Carey

2 ways

=countif(I8:I24,"<90")-=countif(I8:I24,"<=75")

=SUMPRODUCT(--(I8:I24<90),--(I8:I24,<=75))
 
S

sirknightly

Use an array function. Array functions perform individual calculation
on multiple cells simultaneously. But to get them to work, you mus
hold down CTRL and SHIFT while pressing ENTER after typing in th
formula.

Here's the formula. The pointed brackets will be automatically create
by Excel after you hold down CTRL and SHIFT and press ENTER:

=SUM(IF(I8:I24>=75,IF(I8:I24<90,1,0),0))

Knightly
 
S

sirknightly

Use an array function. Array functions perform individual calculation
on multiple cells simultaneously. But to get them to work, you mus
hold down CTRL and SHIFT while pressing ENTER after typing in th
formula.

Here's the formula. Replace "_GT_" and "_LT" with the appropriat
signs. For some reason this site erases them upon posting... Th
pointed brackets will be automatically created by Excel after you hol
down CTRL and SHIFT and press ENTER:

={SUM(IF(I8:I24_GT_=75,IF(I8:I24_LT_90,1,0),0))}

Knightly
 
Top