count if function help

[Matthew Dyer]

I have cells with values ranging from 40-59. I love the countif
function, but I want to do something a little different. I want to see
how many values fall within a certain range (ex. between 50 and 55).
How may I achieve this? I really hate to use the frequency function
becuase of how complicated and rigid it is.

 

[Roger Govier]

Hi Matthew

=COUNTIF(A:A,">49")-COUNTIF(A:A,">55")

--
Regards
Roger Govier

I have cells with values ranging from 40-59. I love the countif
function, but I want to do something a little different. I want to see
how many values fall within a certain range (ex. between 50 and 55).
How may I achieve this? I really hate to use the frequency function
becuase of how complicated and rigid it is.

__________ Information from ESET Smart Security, version of virus
signature database 4772 (20100114) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4772 (20100114) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Dave Peterson]

Count the number bigger than (or equal to???) 50 and subtract the ones that are
too big (bigger than (or equal to????)) 55.



=countif(a1:a10,">"&50) - countif(a1:a10,">"&55)

You may want ">=" in either/both of these expressions

 

[Paul]

XL2000-2010
=COUNTIF($A2:B$25,"<=55")-COUNTIF(A2:$A$25,"<50")

XL2007-2010(Only)
=COUNTIFS($A$2:$A$25,"<=55",$A$2:$A$25,">=50")

 

[Matthew Dyer]

I knew it was going to be something simple like that... I kept trying
to put one countif function inside of another countif function, and as
you can imagine that didnt work out at all. Thanks for all your help
guys!

 

[Matthew Dyer]

Here's another question... I would like to do a sumif using two
different criteria.

ex: sum values of column E if column D is >=58 AND column H is >0

Help..?

 

[Pete_UK]

Well, you can't use SUMIF for that. Use this instead:

=SUMPRODUCT((D2100>=58)*(H2:H100>0),E2:E100)

Adjust the ranges to suit.

Hope this helps.

Pete

 

[Matthew Dyer]

Well, you can't use SUMIF for that. Use this instead:

=SUMPRODUCT((D2100>=58)*(H2:H100>0),E2:E100)

Adjust the ranges to suit.

Hope this helps.

Pete





- Show quoted text -

That works perfectly... but do you mind explaining how this particular
function... um... functions?

 

[Pete_UK]

Hi Matthew,

sorry, I've only just seen your response. Bob Phillips explains the
function very well here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete