countif in a value range?

P

pokdbz

I would like to do a count if a value is within a range like
=COUNTIF(E3:E150,"<=89 and >=80")

But that doesn't work
 
R

Rick Rothstein \(MVP - VB\)

You have to do it in two parts... count the values less than or equal to
your maximum value and subtract the count of the values **less than**
(hence, no equal sign) than your minimum value...

=COUNTIF(E3:E150,"<=89")-COUNTIF(E3:E150,"<80")

Rick
 
R

Ron Coderre

Try one of these:

=SUM(COUNTIF(E3:E150,{"<=89","<80"})*{1,-1})
or
=SUMPRODUCT((E3:E150>=80)*(E3:E150<=89))

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

Regards,

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

Ron Coderre

I considered that approach....but...
If decimals can be in the list, it needs tweaking.
Values between 79 and 80, exclusive, trip it up.
(eg 79.5)

Perhaps:
=INDEX(FREQUENCY(E3:E150,{89,79.9999999999999}),)

--------------------------
Regards,

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