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? |
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? |
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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com