LibreOffice Calc SUM formula: how to sum numbers automatically

Martin Brinkmann
Updated •
Microsoft Office, Tutorials

To sum numbers in LibreOffice Calc, click an empty cell and type =SUM(B1:B5), replacing B1:B5 with the range you want to add, then press Enter. Calc adds every number in that range and updates the total automatically whenever one of the values changes. For a quick total without a formula, select the cells and read the Sum shown in the status bar.

This guide covers the SUM formula syntax, the Sum (AutoSum) button, the Function List sidebar, conditional totals with SUMIF, and the reasons a sum can come out wrong.

LibreOffice Calc spreadsheet with a SUM formula totalling a column of numbers

LibreOffice SUM formula syntax

The LibreOffice Calc SUM function adds numbers, cell references and cell ranges, and it accepts up to 255 arguments. The official syntax is SUM(Number 1; Number 2; ... ; Number 255), where arguments are separated by semicolons.

Common forms of the SUM formula:

  • =SUM(A1:E10) adds every number in the block from A1 to E10.
  • =SUM(A1;A3;B5) adds three individual cells.
  • =SUM(2;3;4) adds the numbers directly and returns 9.
  • =SUM(B1:B5;D1:D5) adds two separate ranges in one formula.

A range is written as first cell, colon, last cell. Instead of typing it, you can type =SUM( and then drag over the cells with the mouse; Calc fills in the reference for you. Close the bracket and press Enter.

To edit an existing formula, select its cell and change it in the input line of the Formula Bar at the top of the sheet, then press Enter again.

Depending on your locale and the formula settings under Tools > Options > LibreOffice Calc > Formula, Calc may also accept a comma as the separator. The semicolon works in every setup, which is why the official help uses it.

SUM formula typed manually into a LibreOffice Calc cell

How to use AutoSum in LibreOffice Calc

AutoSum in LibreOffice Calc is the Select Function button (the sigma icon) next to the input line of the Formula Bar, and it inserts a SUM formula with a suggested range in one click.

  1. Click the empty cell directly below a column of numbers or to the right of a row of numbers.
  2. Click the sigma icon on the Formula Bar and choose Sum from the drop-down list.
  3. Check the range Calc proposes. Drag over different cells if it guessed wrong.
  4. Press Enter or click Accept (the green check mark) to insert the formula.

The same drop-down list also offers Average, Min, Max, Count, CountA, Product, Stdev, StdevP, Var and VarP.

AutoSum has two time-savers. If you select a block of two or more rows first, Calc inserts one total per column into the first empty row below the block. If you select a single row, the total goes into the first empty cell to the right. If the range contains filters, Calc inserts SUBTOTAL instead of SUM, so hidden rows are left out of the total.

The shortcut list in LibreOffice Help does not include an Excel-style Alt+= key for AutoSum. If your copy has none, assign one yourself under Tools > Customize > Keyboard by binding a key to the AutoSum command.

Add SUM from the Function List sidebar

The Function List is a sidebar panel that lists every Calc function, and it is useful when you want to browse functions instead of typing them.

  1. Select View > Function List. The panel opens on the right side of the window.
  2. Pick Mathematical from the category menu, or use the full list, and find SUM.
  3. Click the cell that should hold the result.
  4. Double-click SUM to insert the function into the cell.
  5. Drag over the cells you want to add and press Enter.

For guided input with a field for each argument, use the Function Wizard on the Formula Bar (or Insert > Function) instead. It shows the result of the formula while you build it.

See a sum without a formula in the status bar

The fastest way to total numbers in LibreOffice Calc is to select them: by default, the status bar at the bottom of the window shows the SUM of the selected cells.

LibreOffice Calc status bar showing the sum of selected cells

Right-click that field to change what it displays. The options are Average, count of values (COUNTA), count of numbers (COUNT), Maximum, Minimum, Sum and None.

The status bar total is only a preview. It is not stored in the sheet, it disappears when you change the selection, and you cannot reference it from other cells. Use a SUM formula when the total needs to stay in the document, appear in a printout, or feed other formulas. Formulas also make templates more useful: a SUM in a template recalculates as soon as you enter new figures.

Sum with a condition: SUMIF and SUMIFS

SUMIF adds only the values that meet a criterion, which covers totals such as "all expenses over 100" or "all revenue for one product". Its syntax is SUMIF(Range; Criterion; SumRange), where SumRange is optional.

  • =SUMIF(C2:C6;">=100") adds the values in C2:C6 that are 100 or more.
  • =SUMIF(A2:A6;"pen*";C2:C6) adds the values in C2:C6 where the matching name in column A starts with "pen".

SUMIF supports wildcards or regular expressions in the criterion, depending on the setting under Tools > Options > LibreOffice Calc > Calculate. When you need two or more conditions at once, for example one product in one month, use SUMIFS, which takes a sum range followed by pairs of criterion ranges and criteria.

Why a LibreOffice sum shows the wrong result

A wrong or zero total in LibreOffice Calc is usually caused by numbers stored as text, because the SUM function ignores any text or empty cell within a range.

Work through this checklist:

  • Numbers stored as text: turn on View > Value Highlighting. Real numbers turn blue and text stays black. Imported data and values with a leading apostrophe are typical culprits. Data > Text to Columns or re-entering the values converts them.
  • Wrong range: click the formula cell and check the colored range outline. Rows added below the range are not included automatically.
  • Total does not update: make sure Data > Calculate > AutoCalculate is enabled.
  • Formula shown as text: the cell may be formatted as Text, or the formula is missing the leading equals sign.
  • Hidden or filtered rows: SUM includes them, SUBTOTAL skips filtered rows.

If you switch between Calc and Excel, for example after looking at what Microsoft Office 2024 includes, the SUM, SUMIF and SUMIFS formulas work the same way in both, so spreadsheets that use them open with the totals intact. The full reference is in the LibreOffice Help entry for SUM.

FAQ

Is there a keyboard shortcut for SUM in LibreOffice Calc?

None is listed in LibreOffice Help's Calc shortcut keys. Use the sigma icon on the Formula Bar, or assign your own key to the AutoSum command under Tools > Customize > Keyboard.

Can I sum an entire column in Calc?

Yes. Use a whole-column reference such as =SUM(B:B), but place the formula outside column B, otherwise Calc reports a circular reference.

Does LibreOffice use commas or semicolons in formulas?

The semicolon is the standard function separator in Calc and always works. Whether a comma is also accepted depends on your locale and the separator settings in Tools > Options > LibreOffice Calc > Formula.

Related: jump to the last row with data in Excel and Google Sheets, using LibreOffice as a PDF editor, compare two lists to find the differences

Comments

  1. 🐷 said on
    Reply

    Is the article supposed to be an Advertisement? It repeatedly links to the exact same file four times, and lists prices in several sections.

  2. Peter Louwen said on
    Reply

    FIX THE F***ING COMMENT SECTION F***ERY, DAMMIT!
    RIGHT F***ING NOW!!!!

  3. yanta said on
    Reply

    ah, wonderful, this message/article cross-posting hasn’t been fixed.
    Ignore my comments.

  4. hira said on
    Reply

    iam vary satisfied this work
    http://crackscoop.com

  5. Dennis Faucher said on
    Reply

    HarmonyOS doesn’t run Windows apps. It is no threat to Windows.

    1. Glyde said on
      Reply

      I agree.. i bet it cant even run wallpaper engine, it probably has the worst compatibility with software.

      1. Anonymous said on
        Reply

        Have you ever actually used it?

  6. vanp said on
    Reply

    Who knows where this comment is going to wind up. It’s in response to the article about Huawei’s HarmonyOS (HOS) operating system.

    Two places in the article it says HOS is open-source. One place the article says HOS is proprietary. Uh, I’m pretty sure it can’t be both. Which is it? If there’s some fine distinction, somebody needs to explain it.

    1. owl said on
      Reply

      @vanp,

      Note: I replied to you on September 6, 2023 at Around 2:20 pm, but it was still remain blocked after more than half a day, so I replaced the quoted URI scheme: https:// with “>>” and reposted.

      The current ghacks.net is owned by “Softonic International S.A.” (sold by Martin in October 2019), and due to the fate of M&A, ghacks.net has changed in quality.
      >> ghacks.net/2023/09/02/microsoft-is-removing-wordpad-from-windows/#comment-4573130
      Many Authors of bloggers and advertisers certified by Softonic have joined the site, and the site is full of articles aimed at advertising and clickbait.
      >> ghacks.net/2023/08/31/in-windows-11-the-line-between-legitimate-and-adware-becomes-increasingly-blurred/#comment-4573117
      As it stands, except for articles by Martin Brinkmann and Ashwin, they are low quality, unhelpful, and even vicious. It is better not to read those articles.
      >> ghacks.net/2023/09/01/windows-11-development-overview-of-the-august-2023-changes/#comment-4573033

      By the way, if you use an RSS reader, you can track exactly where your comments are (I’m an iPad user, so I use “Feedly Classic”, but for Windows I prefer the desktop app “RSS Guard”).
      RSS Guard: Feed reader which supports RSS/ATOM/JSON and many web-based feed services.
      >> github.com/martinrotter/rssguard#readme

      Regarding “Huawei’s HarmonyOS” you asked about, the developer has stated that it is planning to open source, however the actual situation has been shelved (no such movement).
      HarmonyOS – Wikipedia
      >> en.wikipedia.org/wiki/HarmonyOS
      Therefore, it is “proprietary software”.
      Moreover, both the company and its production base are in China. China, Russia, Israel, etc. are “Authoritarian state” and products and companies based in those countries are under state control, and privacy policies can become “famous and innocent.” Those products should be avoided.

      1. vanp said on
        Reply

        owl, thanks again for the great info.

      2. owl said on
        Reply

        Correction of sentence
        Before correction: “famous and innocent.”
        After correction: “nominal name only titular.”

    2. Jody Thornton said on
      Reply

      After all of these issues with Ghacks articles and misplaced postings, I’m reminded of this

      https://www.ghacks.net/2019/10/07/ghacks-has-a-new-owner-and-that-is-a-good-for-everyone/

  7. Anonymous said on
    Reply

    It won’t run programs or real games, so it will be useless.
    I mean, there are some people who apparently love using tablets and phones for everything, but mostly are people who will barely do anything with their brain in life.

    I mean, even if Photoshop, and others are available for iPad, do people think iPad is a threat for windows? not really, maybe for useless consumers who will just, like with a phone, be happy and move on, but not for professional industries which are the ones who matter the most, because are the ones who generate more revenue, since they buy the most expensive hardware and software.
    Nothing consumer computing related really makes much money, unless it is data from users that get sold for AI or Ads.

  8. wibble said on
    Reply

    HAHAHAHA – did you really say “it could pose a serious threat to Microsoft’s Windows operating system”?

  9. John G. said on
    Reply

    I won’t trust a foreign OS.

    1. Anonymous said on
      Reply

      “I won’t trust a foreign OS.”

      You trust Microsoft Windows, Google Android and Apple operating systems just because they are from businesses in your country ? Talk about blind nationalism.

      Do not forget also that there is a world outside of USA and that for most of human beings, your favorite operating systems are also of a foreign origin, and as hostile towards them as they are towards yourself.

    2. John G. said on
      Reply

      Deeply foreign, I meant. In so many ways.

  10. Winnie said on
    Reply

    Without windows-based videogames able to run, it’ll stay niche.

    Huawei better put serious money into a harmonyOS port of Wine, DXVK or Proton if it wants its machine being more than web browsing thin clients.

  11. dial said on
    Reply

    “HarmonyOS does not have Google. Huawei’s HarmonyOS is a proprietary operating system..”
    vs
    “HarmonyOS is open-source, which means that it can be customized by developers and manufacturers.”

    Both are quotes from the article. So which one is it, open source or proprietary?

  12. awd said on
    Reply

    this is funny. it looks like the same base article, comments and all has been repurposed and edited or something for 3 (or more) different articles.

    started off as something for some office suite… and then it was some antivirus thing.. and now it’s some iffy os.

    wtf is that writer doing? no one reads or comments on the ad articles and he has to reuse old ones to make it look like it’s getting traction? (though it shows up as 0 comments on the homepage, so that can’t be it?)

  13. Anonymous said on
    Reply

    HarmonyOS is not an open-source OS, only partially components.

    P.S.
    What is happening with these comments from other articles?

  14. Zaqzyp said on
    Reply

    What silly click bait article. Even the actual article is ridiculous.

    “The launch of HarmonyOS for PC could pose a serious threat to Windows.”

    Who cares what people in China use.
    Don’t do drugs before working maybe.

    What’s next? Look up the biggest Android fork in China and write pointless long article about it how it’ll take over Google’s Android.

    1. Zaqzyp said on
      Reply

      HarmonyOS and Excel comments are mixed up.

  15. Anonymous said on
    Reply

    Important note: changing policies through the program doesn’t save them automatically. You need to hit Ctrl+S (or go to File > Sve Policies)

  16. News filter said on
    Reply

    If you want news then add this line in uBlock Origin:

    ghacks.net##.hentry,.home-posts,.home-category-post:not(:has-text(/Martin Brinkmann|Mike Turcotte|Ashwin/))

    1. owl said on
      Reply

      @News filter,
      > ghacks.net##.hentry,.home-posts,.home-category-post:not(:has-text(/Martin Brinkmann|Mike Turcotte|Ashwin/))

      Thanks for the useful information.
      Added line to My filters in uBlock Origin.

      Magnificently,
      I was able to clean up “all articles” by other authors on the Ghacks site.

      It’s refreshing to be able to see only useful articles (instead of being buried among inferior articles) by clearing out the obtrusive articles.

  17. Kirk said on
    Reply

    On a slight tangent, does foobar2000 have a built in lyrics plug in? I mostly used Linux and Deadbeef, the closest alternative to Foobar there has a very old(and kinda broke) plug in.

  18. awf said on
    Reply

    @graham
    surely more importantly is why an ad for dodgy anti-virus has the whole thread on office suites instead.. something in the db is messed up.

  19. Graham said on
    Reply

    Um… When was this article posted? The date says today (August 23, 2023), but I’m seeing comments from 3 years ago.

    1. Anthony said on
      Reply

      I was going to amuse myself and check out the comments for this Avast AV sponsored post since there were so many comments. I thought it funny since they have an article bashing the product in 2019.

      What do I see when I go to the comments? Ghacks pulled an “Amazon” and replaced an article on Excel SUM functions with a sponsored post to make it look like a good article.

      Anyone and everyone who even has a clue about tech knows that Avast is utter garbage and focuses on spying on you and spamming you with ads these days. Just check out the bad article from 2019 on this very site! If you run Windows 10/11, you already have better antivirus than Avast built-in to Windows.

  20. semce said on
    Reply

    I used Excel a lot when I was working. Retired now seven years. Excel was great, especially for macros. I now use LibreOffice, which works similarly but the macros are difficult in comparrison to Excel. I couldn’t find anything on macros for Free Office, which was disappointing, so I uninstalled it. I find Zoho to be the most similar to Excel but there you’re stuck with your spreadsheets in the cloud which I didn’t like.

  21. will willows said on
    Reply

    Ditto, Marty. Everyone starts somewhere. Thanks.

    1. Anonymous said on
      Reply

      Instead of being snarky, maybe you could be helpful. I’d like to add two numbers in a cell;
      In Excel, it would be
      =5+6

      I try that in this piece of crap software, and get a message “Invalid value.”

  22. Mary said on
    Reply

    I would like to know how to make the sum function work. I know how it should work and it works in excel. But when I put the sum function in, it will not make changes when I edit and change the data, the total remains the same

  23. Pat said on
    Reply

    The SUM function doesn’t work. It just keeps putting the same total no matter what figures I enter.

  24. Anonymous said on
    Reply

    A new way to sum with the latest version is to place the cursor under the numbers to be summed, then press Alt-=, then ENTER.

  25. ShintoPlasm said on
    Reply

    To all those complaining about this being a simple ‘101’ function: it’s Martin’s blog, he can write a deep-dive review of Windows Calculator if he so wishes. Do you pay his salary or something?

    1. Klaas Vaak said on
      Reply

      @ShintoPlasm: yes, it is Martin’s blog, but it is a public blog with a comments section, which means he invites people to comment. And having been an avid reader for several years now, I am pretty sure Martin does not expect commenters to agree with him all the time.

      You do not help him with agreeing with everything willy-nilly and “honouring” him with praise. Martin, like everyone else in the world, is not perfect, therefore he can only learn from constructive criticism.

      Unfortunately you look at constructive criticism as a complaint. I do not agree with you on that and will keep making constructive criticism because I believe in keeping this website one of the best ones – refraining from constructive criticism won’t do that.

      1. Kent Brockman said on
        Reply

        Absolutely. People could rather than leaving constructive criticism just stop visiting the site. How is that in the best interest of the site’s authors?

  26. Bart from Holland said on
    Reply

    Thanks Martin for all your great work you put in to make our live easier. Have a nice and healthy 2021.

  27. Anonymous said on
    Reply

    Yes, I use functions in Calc: Average, Min and Max. I use the mouse to select the cells.
    I don’t usually have to use Sum.
    Thanks for the info, and could we have more tips for LibreOffice?
    I find that the documentation does not keep up with the changes in LibreOffice.
    Also, it is hard to find info in the help documentation.

  28. SpywareFan said on
    Reply

    Overall LO has become a good SW, what prevents me to use it is:
    https://wiki.documentfoundation.org/Development/Calc/XMLSource
    “it will not store the information about the data source once the data is imported”
    Until that functionality is implemented I’m forced to use M$ Office (2003).

    1. Kent Brockman said on
      Reply

      Another vote for Softmaker’s Free Office, a very nice alternative to MS Office.

    2. Klaas Vaak said on
      Reply

      @SpywareFan: an interesting alternative for M$ Office is Softmaker’s FreeOffice.

      1. SpywareFan said on
        Reply

        In the FreeOffice manuals there is no reference to XML data mapping functions.

      2. intelligencia said on
        Reply

        @Klaas Vaak

        I have the Softmaker’s Free Office Suite in my computer – – and I LOVE IT!

        intelligencia

  29. Klaas Vaak said on
    Reply

    @Martin Brinkmann: with all due respect, this is now such a basic function, esp. for people reading your articles, that this article is like explaining to the readers how to calculate 1+1.

    1. pHROZEN gHOST said on
      Reply

      You were not born with the knowledge you have now. You once had to learn too.
      So get down off your high horse, grow up and show a little respect for others who need details.

    2. Peterc said on
      Reply

      @Klaas Vaak: In Martin’s defense, it’s the day after Christmas, and some eggnog recipes pack a *real wallop*. Maybe we’re lucky he could type at all. ;-) More seriously, I don’t use Calc super-often, I typically type in the =SUM() function manually, and I’d actually *forgotten* where the Σ button is located in Calc. (The last time I was a heavy spreadsheet user, it was in an older version of Excel and I’m pretty sure the Σ button was somewhere on one of the “regular” toolbars.) So, the article actually did have a small payoff for me. Plus, there might be other intermediate or advanced users of other types of programs (graphics, audio, video, word-processing) who have never used a spreadsheet in their lives. It’s kind of hard to imagine, I know, but it’s possible.

    3. Malte said on
      Reply

      @Klass Vaak & Herman Cost
      Ever thought that people might google it so this artice might show up in the search results. So..no it’s not useless. I think it’s a good strategy explaining stuff even if it’s just basic things. Every day there is a new user on the internet searching for this kind of tutorial.

      1. Klaas Vaak said on
        Reply

        @Malte: you make a good point, in principle. Nevertheless, nowadays telling people how to do a sum calculation, the simplest of the simple, in a spreadsheet is like teaching grandma to suck eggs.

        If say an elderly person would be sitting in front of a spreadsheet wondering how to sum a number of cells, that person is unlikely to look it up on internet, but would ask one of her/his children.

        And in any case, Ghacks is not a spreadsheet tricks and tips site, so it would not show up high in the ranking in a Google search.

      2. yanta said on
        Reply

        I am an elderly person and I can use sum, sumif, sumifs and so on. My kids come to me for knowledge on how to use excel and other programs ;)

        But then, I was in IT my entire career before I retired :)

        There are basic sites, and there are advanced sites. GHacks used to be more for people with technical knowledge. You can’t be everything to everyone though that seems to be the trend these days. Since it’s all about clicks and click bait the wider the topics the more you can remain “relevant”…

        However, that mentality tends to alienate the more technical people. I find myself spending less time here every month. It won’t be long before I stop coming here altogether.

        I have no mobile or wireless technology.

        Anything google or apple is insidious to me. And that accounts now for most of the content here.

        As to this article. Avast, and all of their products are untrustworthy. There has been much controversy and negativity regarding their products. I would think a reputable site would be more careful in what they publish… err, sorry, advertise.

      3. marty said on
        Reply

        I`m an elderly person. 71.
        My experience in computers spans 50 years, focused on Unix internals, advanced degrees.
        I look to the internet for answers, since my children hate me and wouldnt tell me “jack”, even if they knew the answer.
        I dont know how to “suck eggs in principle”, or anything else for that matter. I’m retired and not interested in learning. Some other guy learned “the theory of sucking things.”

        Never needed spreadsheets, always too darn busy. Thanks for this info. Now I know how to sum two cells.

        Everyone starts somewhere.

    4. Herman Cost said on
      Reply

      Yes, I have to agree with this. I can’t imagine that there are many spreadsheet users who are computer literate enough to even casually read ghacks, but don’t know how to use a SUM function.

Leave a Reply

By commenting, you accept our privacy policy and keep the discussion respectful and on topic.