User Profile
Minizv
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Formulas not working sometimes
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?1.4KViews0likes5Comments
Groups
Recent Blog Articles
No content to show