Countif result problem

[CindyW]

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!!!

 

[Jim Cone]

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*"

Re: "...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" <[email protected]>
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!!!

 

[Dave Peterson]

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.

 

[JLatham]

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!

 

[Dave Peterson]

If you retype America in A1 (should read K2)....

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.