Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with a COUNTIF (I think)
Hello, all:
I have a column of about 500 cells, some of which contain numbers, some contain blanks, and some contain the word "none". I want to put a formula in the cell at the top of the colum which counts ONLY those cells which contain numbers. Is there a specific function which will recognize only numbers? Failing that, I assume a COUNTIF is in order. I tried this: =COUNTIF(A2:A500,AND("<""","<none")) but it yields a zero. I've also tried variations moving around and eliminating the double quotes but I can't get it to work. Any suggestions? Help is appreciated. Thanks, MARTY |
#2
|
|||
|
|||
=sumproduct(--isnumber(a2:a500))
"Marty" wrote in message ... Hello, all: I have a column of about 500 cells, some of which contain numbers, some contain blanks, and some contain the word "none". I want to put a formula in the cell at the top of the colum which counts ONLY those cells which contain numbers. Is there a specific function which will recognize only numbers? Failing that, I assume a COUNTIF is in order. I tried this: =COUNTIF(A2:A500,AND("<""","<none")) but it yields a zero. I've also tried variations moving around and eliminating the double quotes but I can't get it to work. Any suggestions? Help is appreciated. Thanks, MARTY |
#3
|
|||
|
|||
Didn't work. Still yields a zero.
I assume you intended me to replace the "--" with the A2:A500 range. Also, not sure why you're suggesting the use of SUMPRODUCT, since all I want to do is count the cells. What am I missing? Please say more. "N Harkawat" wrote: =sumproduct(--isnumber(a2:a500)) "Marty" wrote in message ... Hello, all: I have a column of about 500 cells, some of which contain numbers, some contain blanks, and some contain the word "none". I want to put a formula in the cell at the top of the colum which counts ONLY those cells which contain numbers. Is there a specific function which will recognize only numbers? Failing that, I assume a COUNTIF is in order. I tried this: =COUNTIF(A2:A500,AND("<""","<none")) but it yields a zero. I've also tried variations moving around and eliminating the double quotes but I can't get it to work. Any suggestions? Help is appreciated. Thanks, MARTY |
#4
|
|||
|
|||
No do not replace "--"
just copy the formula offered and paste it as it is... =sumproduct(--isnumber(a2:a500)) IT WILL WORK. "Marty" wrote in message ... Didn't work. Still yields a zero. I assume you intended me to replace the "--" with the A2:A500 range. Also, not sure why you're suggesting the use of SUMPRODUCT, since all I want to do is count the cells. What am I missing? Please say more. "N Harkawat" wrote: =sumproduct(--isnumber(a2:a500)) "Marty" wrote in message ... Hello, all: I have a column of about 500 cells, some of which contain numbers, some contain blanks, and some contain the word "none". I want to put a formula in the cell at the top of the colum which counts ONLY those cells which contain numbers. Is there a specific function which will recognize only numbers? Failing that, I assume a COUNTIF is in order. I tried this: =COUNTIF(A2:A500,AND("<""","<none")) but it yields a zero. I've also tried variations moving around and eliminating the double quotes but I can't get it to work. Any suggestions? Help is appreciated. Thanks, MARTY |
#5
|
|||
|
|||
Take a look at
http://www.mcgimpsey.com/excel/doubleneg.html In article , "Marty" wrote: Didn't work. Still yields a zero. I assume you intended me to replace the "--" with the A2:A500 range. Also, not sure why you're suggesting the use of SUMPRODUCT, since all I want to do is count the cells. What am I missing? Please say more. |
#6
|
|||
|
|||
It worked! Thanks very much.
"N Harkawat" wrote: No do not replace "--" just copy the formula offered and paste it as it is... =sumproduct(--isnumber(a2:a500)) IT WILL WORK. "Marty" wrote in message ... Didn't work. Still yields a zero. I assume you intended me to replace the "--" with the A2:A500 range. Also, not sure why you're suggesting the use of SUMPRODUCT, since all I want to do is count the cells. What am I missing? Please say more. "N Harkawat" wrote: =sumproduct(--isnumber(a2:a500)) "Marty" wrote in message ... Hello, all: I have a column of about 500 cells, some of which contain numbers, some contain blanks, and some contain the word "none". I want to put a formula in the cell at the top of the colum which counts ONLY those cells which contain numbers. Is there a specific function which will recognize only numbers? Failing that, I assume a COUNTIF is in order. I tried this: =COUNTIF(A2:A500,AND("<""","<none")) but it yields a zero. I've also tried variations moving around and eliminating the double quotes but I can't get it to work. Any suggestions? Help is appreciated. Thanks, MARTY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |