Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif result problem
I have Excel 2002 (I know, way out of date, but I have to use what the
company provides) and have suddenly started having problems with a 'countif' formula. The workbook in question has worked fine until last week, when all of a sudden my 'countif' formulas no longer display the result. Here's one of the formulas: =COUNTIF('Team Verification'!$K$2:$K$1355,"AMERICA") The data on the tab 'Team Verification' is in it's proper place (column K). When I put my cursor on the cell containing the COUNTIF formula and click on the AUTOSUM function button to bring up the Function Arguments window, it shows in that window the correct Formula Result, but the result showing in the cell is '0'. I have checked the formatting of the cell (I read through most of these topics!), and have changed it to 'number' or 'general', but neither made a difference. I tried using copies of this workbook from a few weeks back that worked fine then, but the same thing happens when I paste the new data onto the 'Team Verification' worksheet. I am really stumped. The COUNTIF function of Excel is one I've used a lot over the past 10 or 12 years in various versions of Excel. Any help will be greatly appreciated!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif result problem
Has a column been inserted or deleted? Does the new data have "AMERICA" as a single word in a cell in column K? Have you trimmed the new data to remove leading and trailing spaces? You can try adding asterisks... "*AMERICA*" "...Excel 2002 (I know, way out of date, but..." Sounds like you have not tried XL 2007. Be careful what you wish for. <g -- Jim Cone Portland, Oregon USA (Custom sorting with "Special Sort"... http://www.contextures.com/excel-sort-addin.html) "CindyW" wrote in message ... I have Excel 2002 (I know, way out of date, but I have to use what the company provides) and have suddenly started having problems with a 'countif' formula. The workbook in question has worked fine until last week, when all of a sudden my 'countif' formulas no longer display the result. Here's one of the formulas: =COUNTIF('Team Verification'!$K$2:$K$1355,"AMERICA") The data on the tab 'Team Verification' is in it's proper place (column K). When I put my cursor on the cell containing the COUNTIF formula and click on the AUTOSUM function button to bring up the Function Arguments window, it shows in that window the correct Formula Result, but the result showing in the cell is '0'. I have checked the formatting of the cell (I read through most of these topics!), and have changed it to 'number' or 'general', but neither made a difference. I tried using copies of this workbook from a few weeks back that worked fine then, but the same thing happens when I paste the new data onto the 'Team Verification' worksheet. I am really stumped. The COUNTIF function of Excel is one I've used a lot over the past 10 or 12 years in various versions of Excel. Any help will be greatly appreciated!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif result problem
Are you sure you have the string "America" in any of those cells?
If the data has been refreshed, maybe some extraneous spaces (leading/trailing) have shown up. If you retype America in A1 of that sheet, does the formula update? If no, then make sure you have calculation set to automatic: tools|options|calculation tab If yes, then check for those extra characters. CindyW wrote: I have Excel 2002 (I know, way out of date, but I have to use what the company provides) and have suddenly started having problems with a 'countif' formula. The workbook in question has worked fine until last week, when all of a sudden my 'countif' formulas no longer display the result. Here's one of the formulas: =COUNTIF('Team Verification'!$K$2:$K$1355,"AMERICA") The data on the tab 'Team Verification' is in it's proper place (column K). When I put my cursor on the cell containing the COUNTIF formula and click on the AUTOSUM function button to bring up the Function Arguments window, it shows in that window the correct Formula Result, but the result showing in the cell is '0'. I have checked the formatting of the cell (I read through most of these topics!), and have changed it to 'number' or 'general', but neither made a difference. I tried using copies of this workbook from a few weeks back that worked fine then, but the same thing happens when I paste the new data onto the 'Team Verification' worksheet. I am really stumped. The COUNTIF function of Excel is one I've used a lot over the past 10 or 12 years in various versions of Excel. Any help will be greatly appreciated!!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif result problem
Check to see if the workbook has been set to Manual Calculation:
Tools -- Options and then the [Calculation] tab. If it is set to manual, change it to Automatic. It is possible that you or someone else opened a workbook during a session that was set to Manual calculation, then you opened the one you are now having problems with. Because the first book opened during the session was set to manual calc, it forces the others into that mode also. And don't complain about XL 2002 - nice, solid product! "CindyW" wrote: I have Excel 2002 (I know, way out of date, but I have to use what the company provides) and have suddenly started having problems with a 'countif' formula. The workbook in question has worked fine until last week, when all of a sudden my 'countif' formulas no longer display the result. Here's one of the formulas: =COUNTIF('Team Verification'!$K$2:$K$1355,"AMERICA") The data on the tab 'Team Verification' is in it's proper place (column K). When I put my cursor on the cell containing the COUNTIF formula and click on the AUTOSUM function button to bring up the Function Arguments window, it shows in that window the correct Formula Result, but the result showing in the cell is '0'. I have checked the formatting of the cell (I read through most of these topics!), and have changed it to 'number' or 'general', but neither made a difference. I tried using copies of this workbook from a few weeks back that worked fine then, but the same thing happens when I paste the new data onto the 'Team Verification' worksheet. I am really stumped. The COUNTIF function of Excel is one I've used a lot over the past 10 or 12 years in various versions of Excel. Any help will be greatly appreciated!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif result problem
If you retype America in A1 (should read K2)....
Dave Peterson wrote: Are you sure you have the string "America" in any of those cells? If the data has been refreshed, maybe some extraneous spaces (leading/trailing) have shown up. If you retype America in A1 of that sheet, does the formula update? If no, then make sure you have calculation set to automatic: tools|options|calculation tab If yes, then check for those extra characters. CindyW wrote: I have Excel 2002 (I know, way out of date, but I have to use what the company provides) and have suddenly started having problems with a 'countif' formula. The workbook in question has worked fine until last week, when all of a sudden my 'countif' formulas no longer display the result. Here's one of the formulas: =COUNTIF('Team Verification'!$K$2:$K$1355,"AMERICA") The data on the tab 'Team Verification' is in it's proper place (column K). When I put my cursor on the cell containing the COUNTIF formula and click on the AUTOSUM function button to bring up the Function Arguments window, it shows in that window the correct Formula Result, but the result showing in the cell is '0'. I have checked the formatting of the cell (I read through most of these topics!), and have changed it to 'number' or 'general', but neither made a difference. I tried using copies of this workbook from a few weeks back that worked fine then, but the same thing happens when I paste the new data onto the 'Team Verification' worksheet. I am really stumped. The COUNTIF function of Excel is one I've used a lot over the past 10 or 12 years in various versions of Excel. Any help will be greatly appreciated!!! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make an IF function that has the same result as COUNTIF? | Excel Worksheet Functions | |||
How to get result by using CountIF, if there are 3 conditions to b | Excel Worksheet Functions | |||
COUNTIF on result of formula | Excel Discussion (Misc queries) | |||
Using sum(1/countif....) not returning expected result | Excel Worksheet Functions | |||
Subtracting a Countif result from a constant | Excel Worksheet Functions |