Formulas not working sometimes

Copper Contributor

I am an amateur when it comes to Excel but knew enough a few years ago to set up a spreadsheet with multiple tabs to keep track of a collection (currently over 11,000 pieces). This was probably in about the 1996 version (or earlier) of Excel. I have kept this as an .xls file all these years but recently some formulas have stopped working in compatibility mode under 365.

I use excel very simply, only using the SUM and COUNTA functions and, frustratingly, some that had been unchanged for years now no longer work but others, seemingly with the same syntax, do! The more I tried to put it right the worse it got.

I have converted the file to .xlsx  format but it has made no difference, some formulas work, some don’t, seemingly for no reason. I can copy a formula that is working into another cell and it does not work (no the range is not changed). I have tried the help system and appear to be doing things correctly.

To simplify the problem these are what I have tried:

=SUM(C28,C47,C52) – gives correct answer (uses 3 cells with figures typed in)

=SUM(C7,F7) – does not work (both cell have calculated figures thus: =COUNTA(A9:A4075) and =NZBottles!J1946

I do a consolidated addition from several sheets and have tried EVERY combination, old, new and impossible, that I can think of or find (only 3 shown):

=SUM(C7,Micros!I4,NZBottles!J1952,JimBeam!A3)

=SUM(C7,+Micros!I4,+NZBottles!J1952,+JimBeam!A3)

=SUM(C7,[Micros!I4],[NZBottles!J1952],[JimBeam!A3])

I cannot even add two totals on the JimBeam sheet. =SUM(H1376,H1465) – Does not work and I have tried combination after combination. I have even typed the results into the cell below, altered the formula to =SUM(H1377,H1467) and it still does not work. Yes, I have checked that the cells are formulated for numbers not text.

Finally, after hours of work, to get a total, I have transferred the totals to the first sheet and done a simple add! This is not something you should have to do with excel.

I have also had problems with COUNTA but less so than with SUM.

Anybody know what is wrong?

5 Replies

@Minizv When you say "does not work", do you get an error, zero or the a number that doesn't seem to be correct?

 

Just a few tests regarding your example =SUM(C7,F7), what do you get when you just type =C7+F7 ?

And how about:

=ISNUMBER(C7)

and 

=ISNUMBER(F7)

@Riny_van_Eekelen 

 

Thanks for the reply.

ISNUMBERS both return TRUE

=C7+F7 Nothing. Only the formula in the cell. I think I am right in saying that this is how it originally was.

@Minizv Odd! Can you share the file via Onedrive?

Yes I could do and there is nothing confidencial in it. I have never done it before though. Can I do it in such a way that only you see it?

@Minizv You can send via a direct message. Hover over my name tag/icon and press Message, similar that what I see when I hover over yours.

Screenshot 2022-07-14 at 07.50.50.png