Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 54
Default Countif Multiple Criteria

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 341
Default Countif Multiple Criteria

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

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


"hot dogs" wrote:

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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,646
Default Countif Multiple Criteria

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:

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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Countif Multiple Criteria

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

--
HTH

Bob Phillips

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

"hot dogs" wrote in message
...
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?



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 54
Default Countif Multiple Criteria

Thanks Bob,

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"hot dogs" wrote in message
...
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?






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Countif Multiple Criteria

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)

"hot dogs" wrote in message
...
Thanks Bob,

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"hot dogs" wrote in message
...
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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use multiple criteria with COUNTIF: between dates and not blank l.shields Excel Worksheet Functions 13 July 27th 09 06:53 PM
Countif using criteria in multiple columns ImaGina Excel Worksheet Functions 1 September 14th 06 07:31 PM
Multiple Criteria using countif calmo Excel Worksheet Functions 6 October 25th 05 08:33 PM
multiple criteria in one field 4a,4b etc of countif? Anne-Marie Excel Discussion (Misc queries) 1 October 20th 05 02:23 PM
Multiple CountIf Criteria JerryS Excel Worksheet Functions 1 October 14th 05 07:14 PM


All times are GMT +1. The time now is 01:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"