Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif problem
I am trying to count all the numbers in a column that equal or exceed a
number that is entered manually in an individual cell (A1). But the countif function does not work unless I enter the number directly into the formula. For example: Assume there is the number 5 in cell A1. I want to count how many cells in range A4:A20 contain a number equal to or greater than the number in cell A1. When I enter the following formula in Cell A21 it returns "0" even though there are some cells in the column A4:A20 that contain a number equal to or greater than 5. The formula I enter in cell A21 is: countif(A4:a20,"=A1") This formula does not work unless I replace the cell reference "A1" with the number I am using as the hurdle number - in this case the number is 5. Is there some way to count the numbers in a column by making the count function use a number in a defined cell as the hurdle number? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif problem
Try this:
=COUNTIF(A4:A20,"="&A1) Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Portocar" wrote in message ... I am trying to count all the numbers in a column that equal or exceed a number that is entered manually in an individual cell (A1). But the countif function does not work unless I enter the number directly into the formula. For example: Assume there is the number 5 in cell A1. I want to count how many cells in range A4:A20 contain a number equal to or greater than the number in cell A1. When I enter the following formula in Cell A21 it returns "0" even though there are some cells in the column A4:A20 that contain a number equal to or greater than 5. The formula I enter in cell A21 is: countif(A4:a20,"=A1") This formula does not work unless I replace the cell reference "A1" with the number I am using as the hurdle number - in this case the number is 5. Is there some way to count the numbers in a column by making the count function use a number in a defined cell as the hurdle number? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif problem
Your formula needs to be written like this:
=COUNTIF(A4:A20,"="&A1) If you include the A1 within the quotes, then Excel will treat it as a literal string, i.e. "A1" rather than the cell reference A1. Hope this helps. Pete "Portocar" wrote in message ... I am trying to count all the numbers in a column that equal or exceed a number that is entered manually in an individual cell (A1). But the countif function does not work unless I enter the number directly into the formula. For example: Assume there is the number 5 in cell A1. I want to count how many cells in range A4:A20 contain a number equal to or greater than the number in cell A1. When I enter the following formula in Cell A21 it returns "0" even though there are some cells in the column A4:A20 that contain a number equal to or greater than 5. The formula I enter in cell A21 is: countif(A4:a20,"=A1") This formula does not work unless I replace the cell reference "A1" with the number I am using as the hurdle number - in this case the number is 5. Is there some way to count the numbers in a column by making the count function use a number in a defined cell as the hurdle number? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif problem | Excel Discussion (Misc queries) | |||
Countif Problem | Excel Worksheet Functions | |||
Countif Problem | Excel Discussion (Misc queries) | |||
countif problem | Excel Discussion (Misc queries) | |||
problem with countif | Excel Worksheet Functions |