| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF counting incorrectly
Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000
with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
What do you get with this formula:
=SUMPRODUCT(--(ISNUMBER(Current!D1:D10000)),--(Current!D1:D10000=180)) -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Maybe some of the cells are really text, although they look like numbers.
You can find out with the ISTEXT() function. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave" wrote in message ... | Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 | with the COUNTIF function. Simply put, the number is not coming up | correctly. I have a very simple function: | | =COUNTIF(Current!D1:D10000,"=180") | | which should give me everything in the column with a value of 180 or | greater, however, the number is off by a few. I'm getting 1531 when there | 1537 records when I simply use the cursor to count. I found a Knowledge base | article that was possibly on it, but was unable to make the formula work | based on their suggestions. The last line of the data currently ends at 3700 | something, but it will continue to grow, thus my 10k figure. Even changing | this to the last line does not alter the incorrect count. | | Please advise! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Dave
there is probably something wrong with your data in d1 - Dxxx Put this in e1 or another helper column =ISNUMBER(D1) drag down and it should return TRUE for each number and FALSE for text. Mike "Dave" wrote: Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
I get just "0" as a value with this.
"T. Valko" wrote: What do you get with this formula: =SUMPRODUCT(--(ISNUMBER(Current!D1:D10000)),--(Current!D1:D10000=180)) -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Mike,
I get all true for my fields with data, however after line 3670 (last night filled in), I get false, as I would expect. I've used countif before without having it count blank fields (that would be how it works, I thought). "Mike H" wrote: Dave there is probably something wrong with your data in d1 - Dxxx Put this in e1 or another helper column =ISNUMBER(D1) drag down and it should return TRUE for each number and FALSE for text. Mike "Dave" wrote: Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Slight correction - d1 has a header, and gives me a FALSE, however cutting it
out and making the formula d2 to d10k does not change the incorrect count. "Dave" wrote: Mike, I get all true for my fields with data, however after line 3670 (last night filled in), I get false, as I would expect. I've used countif before without having it count blank fields (that would be how it works, I thought). "Mike H" wrote: Dave there is probably something wrong with your data in d1 - Dxxx Put this in e1 or another helper column =ISNUMBER(D1) drag down and it should return TRUE for each number and FALSE for text. Mike "Dave" wrote: Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Ok, there's definitely a problem with your data.
I see other replies have made some suggestions but at this point I'd need to see the data first hand to figure out what the problem is. If you can/want to send a copy of the file I'll take a look. I'm at: xl can help at comcast period net Remove "can" and change the obvious. If the file is 1mb in size, zip it. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I get just "0" as a value with this. "T. Valko" wrote: What do you get with this formula: =SUMPRODUCT(--(ISNUMBER(Current!D1:D10000)),--(Current!D1:D10000=180)) -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Due to the nature of the data, I was copying pieces of it into another
spreadsheet. Doing this, and posting them as values, rather than the formulas used to fill them and saw that I was getting non whole numbers (which were expected). On a whim, I used ROUNDUP, since any fraction might as well be another whole unit, and after using this I now have the correct numbers. Thanks for all your help, both you and others. Trying to send you the data actually helped me fix it! "T. Valko" wrote: Ok, there's definitely a problem with your data. I see other replies have made some suggestions but at this point I'd need to see the data first hand to figure out what the problem is. If you can/want to send a copy of the file I'll take a look. I'm at: xl can help at comcast period net Remove "can" and change the obvious. If the file is 1mb in size, zip it. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I get just "0" as a value with this. "T. Valko" wrote: What do you get with this formula: =SUMPRODUCT(--(ISNUMBER(Current!D1:D10000)),--(Current!D1:D10000=180)) -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF counting incorrectly
Glad you got it straightened out! Thanks for letting us know. We appreciate
feedback! -- Biff Microsoft Excel MVP "Dave" wrote in message ... Due to the nature of the data, I was copying pieces of it into another spreadsheet. Doing this, and posting them as values, rather than the formulas used to fill them and saw that I was getting non whole numbers (which were expected). On a whim, I used ROUNDUP, since any fraction might as well be another whole unit, and after using this I now have the correct numbers. Thanks for all your help, both you and others. Trying to send you the data actually helped me fix it! "T. Valko" wrote: Ok, there's definitely a problem with your data. I see other replies have made some suggestions but at this point I'd need to see the data first hand to figure out what the problem is. If you can/want to send a copy of the file I'll take a look. I'm at: xl can help at comcast period net Remove "can" and change the obvious. If the file is 1mb in size, zip it. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I get just "0" as a value with this. "T. Valko" wrote: What do you get with this formula: =SUMPRODUCT(--(ISNUMBER(Current!D1:D10000)),--(Current!D1:D10000=180)) -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi there excel gurus. I'm stumped as to how to rectify a bug in Excel2000 with the COUNTIF function. Simply put, the number is not coming up correctly. I have a very simple function: =COUNTIF(Current!D1:D10000,"=180") which should give me everything in the column with a value of 180 or greater, however, the number is off by a few. I'm getting 1531 when there 1537 records when I simply use the cursor to count. I found a Knowledge base article that was possibly on it, but was unable to make the formula work based on their suggestions. The last line of the data currently ends at 3700 something, but it will continue to grow, thus my 10k figure. Even changing this to the last line does not alter the incorrect count. Please advise! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF not counting | Excel Discussion (Misc queries) | |||
| countif counting properties | Excel Discussion (Misc queries) | |||
| Countif and Text - not counting | Excel Discussion (Misc queries) | |||
| Using COUNTIF for Counting Words? | Excel Worksheet Functions | |||
| counting values within given interval - countif? | Excel Discussion (Misc queries) | |||
Linear Mode
