CountIf formula results in the formula itself being displayed.

I work in large Excel data files where I frequently do a "countif" to compare
account numbers. Sometimes, the "countif" function will result in displaying
the actual formula in the cell, rather than the result. I have tried to make
sure the cells are formatted correctly, etc., but nothing works. Any ideas??

Is it only the COUNTIF formula that shows up as text or does any formula show up as text? If any formula shows up as text than the cell you entered the formula in was probably formatted as text prior to entering it. Delete the COUNTIF formula and check that cell's format. If it is Text then change it to General and re-enter your formula. If it is not text then check under Tools/Options/View Tab to see if in the Window View area the check box next to formulas is selected. If it is, all formulas in your sheet will be visible and not their results.

HTH

Cheers,

Steve

Hi

One possibility is that your sheet is simply displaying formulas. When this
occurs, try hitting Ctrl ` The ` is on the key next to the number 1 key.

Hope this helps.
Andy.

"NewKid" <NewKid@discussions.microsoft.com> wrote in message
news:AF8A486F-428A-4CBC-9491-31C74A174CEC@microsoft.com...
>I work in large Excel data files where I frequently do a "countif" to
>compare
> account numbers. Sometimes, the "countif" function will result in
> displaying
> the actual formula in the cell, rather than the result. I have tried to
> make
> sure the cells are formatted correctly, etc., but nothing works. Any
> ideas??

Thanks! It is only the COUNTIF function that does this. All my other
functions and formulas are displaying normally. Also, MOST of the time, this
doesn't happen, but every once in awhile, it just does. I have formatted to
general, BUT usuallly the account numbers in these columns are preceeded by
zeros and when I format to General and recalc, it drops the first zero...

I have also "cleared" the formats, but that doesn't seem to do it either.
Do you think it could be something silly like my worksheet is just so large
(I frequently have 40,000 plus rows and at least 100 columns.)

Thanks again. Any further ideas are appreciated.
Mary

"SteveG" wrote:

>
> Is it only the COUNTIF formula that shows up as text or does any formula
> show up as text? If any formula shows up as text than the cell you
> entered the formula in was probably formatted as text prior to entering
> it. Delete the COUNTIF formula and check that cell's format. If it is
> Text then change it to General and re-enter your formula. If it is not
> text then check under Tools/Options/View Tab to see if in the Window
> View area the check box next to formulas is selected. If it is, all
> formulas in your sheet will be visible and not their results.
>
> HTH
>
> Cheers,
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=495346
>
>

Thanks Andy.
I did try that, but it didn't make a difference. Any other ideas? Thanks
for your help.
Mary

"Andy" wrote:

> Hi
>
> One possibility is that your sheet is simply displaying formulas. When this
> occurs, try hitting Ctrl ` The ` is on the key next to the number 1 key.
>
> Hope this helps.
> Andy.
>
> "NewKid" <NewKid@discussions.microsoft.com> wrote in message
> news:AF8A486F-428A-4CBC-9491-31C74A174CEC@microsoft.com...
> >I work in large Excel data files where I frequently do a "countif" to
> >compare
> > account numbers. Sometimes, the "countif" function will result in
> > displaying
> > the actual formula in the cell, rather than the result. I have tried to
> > make
> > sure the cells are formatted correctly, etc., but nothing works. Any
> > ideas??
>
>
>

Hi

The only other option I can think of is that the cell is formatted as Text
before you enter the formula. You would need to change the format to General
and then re-enter the formula.

Andy.

"NewKid" <NewKid@discussions.microsoft.com> wrote in message
news:99DAF634-4258-4A74-B3C2-8FD41FC542E9@microsoft.com...
> Thanks! It is only the COUNTIF function that does this. All my other
> functions and formulas are displaying normally. Also, MOST of the time,
> this
> doesn't happen, but every once in awhile, it just does. I have formatted
> to
> general, BUT usuallly the account numbers in these columns are preceeded
> by
> zeros and when I format to General and recalc, it drops the first zero...
>
> I have also "cleared" the formats, but that doesn't seem to do it either.
> Do you think it could be something silly like my worksheet is just so
> large
> (I frequently have 40,000 plus rows and at least 100 columns.)
>
> Thanks again. Any further ideas are appreciated.
> Mary
>
> "SteveG" wrote:
>
>>
>> Is it only the COUNTIF formula that shows up as text or does any formula
>> show up as text? If any formula shows up as text than the cell you
>> entered the formula in was probably formatted as text prior to entering
>> it. Delete the COUNTIF formula and check that cell's format. If it is
>> Text then change it to General and re-enter your formula. If it is not
>> text then check under Tools/Options/View Tab to see if in the Window
>> View area the check box next to formulas is selected. If it is, all
>> formulas in your sheet will be visible and not their results.
>>
>> HTH
>>
>> Cheers,
>>
>> Steve
>>
>>
>> --
>> SteveG
>> ------------------------------------------------------------------------
>> SteveG's Profile:
>> http://www.excelforum.com/member.php...fo&userid=7571
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=495346
>>
>>

You shouldn't need to change all of the formats, just the one where you are entering your COUNTIF function. I just did a COUNTIF on a spreadsheet with 50,000 plus rows and it worked fine.

Steve

I just "cleared" all formats, then tried it, and it still didn't work. Then
I copied and pasted the data only into a new sheet, and it worked!!

Not sure what this is all about, and it must have something to do with
formatting of cells, but in any case, thanks for all your help!

I'm set - for today anyway. :-)

Mary

"Andy" wrote:

> Hi
>
> The only other option I can think of is that the cell is formatted as Text
> before you enter the formula. You would need to change the format to General
> and then re-enter the formula.
>
> Andy.
>
> "NewKid" <NewKid@discussions.microsoft.com> wrote in message
> news:99DAF634-4258-4A74-B3C2-8FD41FC542E9@microsoft.com...
> > Thanks! It is only the COUNTIF function that does this. All my other
> > functions and formulas are displaying normally. Also, MOST of the time,
> > this
> > doesn't happen, but every once in awhile, it just does. I have formatted
> > to
> > general, BUT usuallly the account numbers in these columns are preceeded
> > by
> > zeros and when I format to General and recalc, it drops the first zero...
> >
> > I have also "cleared" the formats, but that doesn't seem to do it either.
> > Do you think it could be something silly like my worksheet is just so
> > large
> > (I frequently have 40,000 plus rows and at least 100 columns.)
> >
> > Thanks again. Any further ideas are appreciated.
> > Mary
> >
> > "SteveG" wrote:
> >
> >>
> >> Is it only the COUNTIF formula that shows up as text or does any formula
> >> show up as text? If any formula shows up as text than the cell you
> >> entered the formula in was probably formatted as text prior to entering
> >> it. Delete the COUNTIF formula and check that cell's format. If it is
> >> Text then change it to General and re-enter your formula. If it is not
> >> text then check under Tools/Options/View Tab to see if in the Window
> >> View area the check box next to formulas is selected. If it is, all
> >> formulas in your sheet will be visible and not their results.
> >>
> >> HTH
> >>
> >> Cheers,
> >>
> >> Steve
> >>
> >>
> >> --
> >> SteveG
> >> ------------------------------------------------------------------------
> >> SteveG's Profile:
> >> http://www.excelforum.com/member.php...fo&userid=7571
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=495346
> >>
> >>
>
>
>

Just a Thought.

Try clicking on the cell that is displaying the formula, then press F2.
Next press enter.

Regards,
Paul

"NewKid" <NewKid@discussions.microsoft.com> wrote in message
news:AF8A486F-428A-4CBC-9491-31C74A174CEC@microsoft.com...
>I work in large Excel data files where I frequently do a "countif" to
>compare
> account numbers. Sometimes, the "countif" function will result in
> displaying
> the actual formula in the cell, rather than the result. I have tried to
> make
> sure the cells are formatted correctly, etc., but nothing works. Any
> ideas??

New

Either you are in Formula View or the formula cells are formatted as text.

Changing the Format alone won't do the trick.

For the first, hit CTRL + `(back quote above TAB key) to toggle View Formulas
on/off.

For the second, Format the cells to General then F2 and ENTER to see results.

If you have already done a great whack of these formulas, select all and
Edit>Replace

what: =
with: =

Replace All and OK.


Gord Dibben Excel MVP

On Wed, 21 Dec 2005 13:20:02 -0800, NewKid <NewKid@discussions.microsoft.com>
wrote:

>I work in large Excel data files where I frequently do a "countif" to compare
>account numbers. Sometimes, the "countif" function will result in displaying
>the actual formula in the cell, rather than the result. I have tried to make
>sure the cells are formatted correctly, etc., but nothing works. Any ideas??

ME too, and it is 12 years later. I have other formulas and it works fine.
When constructing a COUNTIF function the function value shows properly in the window.
Only the function shows in the cell.
I copied the format from another function (COUNT) cell so both cells have the same format.
my range is C2:C44 and my test is "s".
Why does this still not work after 12 years?