Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF....how to use =0 but <9 as CRITERIA
I cannot figure out how to use two criteria with the COUNTIF function. Here
is what I need to accomplish: I have a list of 2000 numbers in column A, the numbers are between 0 and 99(inclusive). I want to see how many numbers are in the following ranges: 1. greater than or equal to 0, less than 9 2. greater than or equal to 10, less than 19 3. greater than or equal to 20, less than 29 .. .. .. .. .. .. 9.greater than or equal to 90, less than 99 Using the COUNTIF function how do you nest =0 but <9 to achieve the results for my first range? I have tried the following two versions of the function to no avail: =COUNTIF(A1:A2000,"=0,<9") =COUNTIF(A1:A2000,"AND(=0,<9)") Does the COUNTIF function only allow one variable as the criteria?? Any suggestions or a solution would be appreciated. THANKS |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF....how to use =0 but <9 as CRITERIA
Hi,
If you want to use countif you use 2 of them like this =COUNTIF(A1:A34,"=0")-COUNTIF(A1:A34,"9") The first counts everything = to your lower number and the second subtracts everything larger than your largest. Mike "Jay" wrote: I cannot figure out how to use two criteria with the COUNTIF function. Here is what I need to accomplish: I have a list of 2000 numbers in column A, the numbers are between 0 and 99(inclusive). I want to see how many numbers are in the following ranges: 1. greater than or equal to 0, less than 9 2. greater than or equal to 10, less than 19 3. greater than or equal to 20, less than 29 . . . . . . 9.greater than or equal to 90, less than 99 Using the COUNTIF function how do you nest =0 but <9 to achieve the results for my first range? I have tried the following two versions of the function to no avail: =COUNTIF(A1:A2000,"=0,<9") =COUNTIF(A1:A2000,"AND(=0,<9)") Does the COUNTIF function only allow one variable as the criteria?? Any suggestions or a solution would be appreciated. THANKS |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF....how to use =0 but <9 as CRITERIA
Hi Jay,
You can use Frequency function . Do one thing in column b1: b10 put values 9,19,29 --- 99 then in cell c1 put this formula =FREQUENCY(a1:a2000,b1:b10) and copy down upto c10 and then enter as array formula . note : select the range c1:c10 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. Harshawardhan . Shastri India ================================================== ========== "Jay" wrote: I cannot figure out how to use two criteria with the COUNTIF function. Here is what I need to accomplish: I have a list of 2000 numbers in column A, the numbers are between 0 and 99(inclusive). I want to see how many numbers are in the following ranges: 1. greater than or equal to 0, less than 9 2. greater than or equal to 10, less than 19 3. greater than or equal to 20, less than 29 . . . . . . 9.greater than or equal to 90, less than 99 Using the COUNTIF function how do you nest =0 but <9 to achieve the results for my first range? I have tried the following two versions of the function to no avail: =COUNTIF(A1:A2000,"=0,<9") =COUNTIF(A1:A2000,"AND(=0,<9)") Does the COUNTIF function only allow one variable as the criteria?? Any suggestions or a solution would be appreciated. THANKS |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF....how to use =0 but <9 as CRITERIA
Harshawardhan and Mike thanks for the info.
Mike yours works as suggested. thanks for the info on FREQUENCY function. Harshawardhan is it possible for you to send me an excel sheet with what you describe in it? Possibly make a list of 30 or so random numbers between 0 and 99..write the coding for the frequency function and email it to me. If you have the time? My email is THANKS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with more than one criteria | Excel Discussion (Misc queries) | |||
CountIf - 2 Criteria | Excel Discussion (Misc queries) | |||
Countif With Criteria | Excel Worksheet Functions | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |