Countif Multiple Criteria

[Guest]

I'm having trouble using the countif function. I have a list of numbers with
blanks, I want to count how many are in the range 0-50 how many are in 51-100
how many are in 101-150 and so on. I have no problem with the
=countif(C1:C50,"<=50") function for the first range, but when I
enter=countif(C1:C50,"AND(>=51,<=100)") I get 0 for my answer but i know
there are values that are greater than 51 and less than 100 in the list. i
have also tried moving the "" around different parts of the AND function and
tried removing them but non of this has worked.

Can anyone give me a clue?

 

[Guest]

Try this
=SUMPRODUCT(--(C1:C50>50),--(C1:C50<100))

explanation is here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Say your numbers are in A2:A14, then create a scale in B1:B5 like this

0
50
100
150
200

select C1:C5, insert formula =FREQUENCY($A$2:$A$14,$B$2:$B$5) into formula
bar (ADJUST RANGES!) and confirm it with Ctrl-Shift-Enter (it's an array
formula)!

Regards,
Stefi


„hot dogs†ezt írta:

 

[Bob Phillips]

=COUNTIF(C1:C50,">=51")-COUNTIF(C1:C50,">100")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob,

I prefer your solution to the others suggested, as its simple and obvious.
Cheers

 

[Bob Phillips]

I agree, and it's fast (which matters in a big spreadsheet)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)