Pages of the blog

17 November, 2016

SUM, AutoSum and some other secrets of LibreOffice Calc

When I started to write this article, I thought it would be brief note for beginners. But in the process of writing, I found quite a lot of information that was illuminated within this theme. As a result, the article grew to pretty large size. And I hope that the material presented here will be useful not only to people beginning their way into Calc, but also for people who have long enjoyed a spreadsheet.

what are we talking about?

Estimation of a sum

If we just need to "estimate" a sum of a certain range, we can use the built-in capability to LibreOffice Calc. The status bar of the program has a field in which "Count:0; Sum: 0" is seen by default . If we select cells on a sheet with numeric values then instead of zero near Sum in this field will be displayed the sum of these numbers. The “Count” will display how much we have selected cells. To select cells separated from each other, or other ranges of cells, we can do by holding the Ctrl key and to clicking mouse on them. Minus of this method is that it takes only sum of a current sheet.

If you click the right button of mouse on the field, you will see the list presented in the screenshot. To discuss the list now, I will not. If you want, you just can play around with it, and, I think, you will understand everything.

SUM function

In LibreOffice Calc, SUM function has the same form as in other spreadsheets such as Excel or Apache OpenOffice. Its syntax is follow:

SUM(Number1; Number2; ...; Number30)

In this case, we just list numbers with the semicolon. Listings of numbers can be explicit (1, 2, 3, ...) can be as cell references (A1; C6; AZ190), and can be mixed (1; A1; C6; 3). The function will "take out" the numerical value and add up it.
The function can be used like this:


In this case, we specify the first (the top left) and last (the bottom right) cells in the range. The formula will determine the rectangle of the range and calculate the sum.
You can even use such:


The third way I see very rarely, but it is often convenient. Select the range of cells, go to the menu Data→ Define Range... In the field Name, type a new range name. Pay attention to flags "Contains column labels" and "Contains totals row", the first of which is set by default. Do not forget to remove or put on them respective your situation. Click OK. Now it is possible to type the range name in the function as an argument instead selecting a range by the mouse.
Another way is to use the column headers or row:

SUM (Column_Label)

This method I see even less, perhaps because it is disabled by default. If you want to use it, go to the menu Tools → Options into section LibreOffice Calc → Calculate and set "Automatically find columns and rows labels." This method is useful when the range is a single column or row, but we don’t know how long will be it. But we must remember that the range should not be interrupted. In LibreOffice Calc an empty row/column over the entire width or height of the range indicates that the range is over, and further it's a different range. Today (version 5.2.3) function is not implemented to end, for example, it does not support the drag range.
Although there are many ways, nobody forbids to use all methods at once. And I think it makes no sense to remind that the links can be not only for the range in the current sheet, but also into other sheets, and even to other files, it does not matter they are in a local computer or somewhere in the network.

AutoSum in LibreOffice Calc

AutoSum in LibreOffice Calc can perform automatic summation only continuous row or column range. Herewith, it will search a range to the vertically upwards and to the horizontally left. But since, in fact, it just inserts the SUM function with the range as an argument, and leaves it open for editing, this function is often convenient, especially if you prefer to work with the mouse. Its use is the same as in other modern spreadsheet programs (for example, Excel). There is the icon with the image of the Greek letter sigma (Σ) on the formula bar. Clicking on this icon activates the AutoSum.
Let's look at an example of a vertical range with two columns:

Selecting the cell under the second column of the range, and clicking the button AutoSum (Σ), we can see that the function has found a continuous range in the column, but has not defined the full range, and remained in the edit state. The screenshot shows a little blue squares at the corners of the range frame. If we pull for the squares by the mouse, we can change the range of values involved in the calculation. That is, if we need to do so that the two columns is involved in the calculations, we can drag for the left upper or lower square into the left to include the second column. Same, we can reduce the range by the vertical in the case, if participate entire vertical range should not be in our calculations. Naturally, we can edit the formula directly, as above. For horizontal bands will look likewise. When you finish to select the range, press the Enter of the keyboard or the green check mark in the formula bar.

SUM as an Array Functions and summation by condition

In the Help to the program (go to the menu Help → LibreOffice Help, or press F1) or in the online Help site, you can find an example of a non-standard use of the SUM function. In my opinion this example is a very well. And I would like to paraphrase it to you.
Let's say we have a table: the first column is dates, and the second is spending in this day. But we would like to know how much we have spented for a particular month, or week, or year. That is, we need to sum the spending for a certain period. I am attaching a file with an example, so you can see how it works. And here I will explain everything in words. There is the table.

In cell I2, the formula =SUM((A3:A300>=F1)*(A3:A300<=F2)*B3:B300) is written. The part of formula (A3:A300>=F1) is condition after which date the numbers will sum. The second part of formula (A3:A300<=F2) is before what date. The cell A3 is the beginning of the date range, the A300 is the end of the range, for example, 300 is even a lot, but in reality it is alleged cell, how far you want to extend your range. Multiplying these two parts, we obtain a matrix of truth, where the numbers which meet the required date will be equal to 1, and the rest 0. In the third part, we have an array of costs and, of course, if we multiply by zero, then the answer is zero, and if by one, the value will be equal to the cell. Thus we get a matrix with zeros and the desired value. That is, if it's all represented in expanded form, it is something like SUM(0, 0, ..., 0, the selected value; 0; 0; ...; 0). In order for formula starts to work, we need to make it as an array function, for that, instead of the usual pressing Enter, we need to press the key combination of Ctrl + Shift + Enter. A sign that you've done everything correctly, is the conclusion of the formula in curly braces ({=SUM((A3: A300>=F1)*(A3:A300<=F2)*B3:B300)}). Do not expose the curly braces yourselves with your hands, because it does not help. You should complete the entry of the formula correctly.
If you use the Automatically find columns and rows labels, then this formula can be rewritten as follows:


After completing as Ctrl + Shift + Enter, we get the same result. But we do not need to worry about the range size, and most importantly it is easy to change and automatically receive updated results. The effect of this example, a search for multiple conditions, also can be achieved with using SUMIFS formula.
Let me give another example. In early 2011 the bug report 35636, related to the fact that the SUMIF function doesn’t accept the empty ("") as a condition, was opened. This bug was fixed only in April 2015. One way workaround of this problem was to use the SUM function as Array function into the form:


I think it is clear that the second part create a true matrix, which is multiplied and makes all of unnecessary values to zero. And, as a result, we get the sum of the values corresponding to the empty cells.

Automatic extension of the range with the addition of cell-to-end

LibreOffice Calc automatically expands the formula range with the addition of the cells in the range center. But if we add up the cells at a range end, for example, using AutoSum, and then try to add a cell to the range end, then Calc does not include it into the range.
A workaround of this limitation, we can use a reference to the range end by OFFSET function. I will not talk on this function in detail now, I will give only its syntax and necessary explanations for this example.

=OFFSET(Reference; Rows; Columns; Height; Width)

Reference is a cell or a range of cells from which begins to shift. Rows is a shift in the rows from the reference, positive numbers are slipping down, negative to up. Columns is shift columns from the reference, positive to the right, negative to the left. The other parameters are not important for this example. In this example, the reference is the cell in which the written the SUM formula. For the rows shift, we point to the last cell in the range of summation. The columns in this example, we do not touch. As a result, we get a formula like this:

=SUM(A2:OFFSET(A6; -1; 0))

Using the Automatically find columns and rows labels, it does not work as is expected. The formula cannot be closely to the range. In addition, to add a row or cell is necessary with a blank cell between the formula and the range, but not with the formula line. As you can imagine, this behavior does not bring the expected result and does not allow users to use an intuitive tool. The formula of this example looks like this:


Amount of cumulative total

The amount of the cumulative total is one of the most frequent task to be solved, especially in models of investment projects. It can be made with various methods, but in my opinion, the most convenient way is to use a range (reference) part as static in the SUM function, and drag it. Suppose we have a cash flow - the money given to the child of pocket expenses (operating expenses unscheduled :)
The formula is entered in cell B4 , it points to the boundaries of the range B3:B3, and we fix the first part by pressing F4 on the keyboard (when the cursor is in the first part):


When dragging, the first cell points to the range beginning, remains in place, while the second (the end of the range) changes automatically in each new cell, forming the sum of all previous values.

Sum Across Multiple Sheets (3-D reference)

When the number of sheets of this spreadsheet book has the same table, we can use a summation of the three-dimensional reference. Sometimes this operation is called "Calculating Across Multiple Sheets" in the forums. For three sheets (Sheet1, Sheet2, Sheet3), adding 3 cells A1 can be as follows:


If we want to sum the range A1:B2 on 3 sheets, it will look like this:




Both ways will work identically. But please note that sheets, for which the summation must stand side by side, and the sum extends from the leftmost to the rightmost inclusive.

SUMIF function – summation by a condition

If we need to add up the numbers corresponding to a single specified condition, then the easiest way is to use the SUMIF function. Its syntax is following:

=SUMIF(Condition_Range; Condition; Sum_Range)

The range of conditions is a range in which a condition is searched. The condition is a condition itself, what we search. Note that the condition is string, that is it must be enclosed in quotes. By the way, it supports regular expressions, which significantly extends the application of this formula. The summation range is a range from which the sample for a given condition is added up.
A simple example, let's say there are a range of cash flow, positive numbers are profits, the negative are the cost, and we want to know how much profits are, that is to add up only positive numbers in the range:

=SUMIF('Cash Flow'; ">0"; 'Cash Flow')

Or, "how to add up the (not) empty cells" is popular question in forum. The idea that we have in the first range are looking for (not) an empty cell, and in the second we add up the corresponding values. For the empty sells:

=SUMIF(Range_Search; ""; Sum_Range)

And for the not empty cells:

=SUMIF(Range_Search; "<>"; Sum_Range) As you can imagine, we can define the range of all possible ways: find in labels, named ranges, simply by specifying the range, even if the range is in another file somewhere on the Internet.

Using regular expressions (regex) in the condition

Form 5.3 version of LibreOffice, regular expressions will turn off by default. In order to turn on this convenient potential to go the menu Tools → Options → Calc → Calculate and select "Enable regular expressions in formula".
A condition of search in LibreOffice Calc supports regular expressions. Writing regular expressions in Calc is different from writing them in Excel and a little bit different from the usual writing them in the shell. Details on regular expressions described on this page.
Most often, we have to add up any one kind of things(eg closed stock, income or selling pencils, notebooks). Let's make a simple table with headings and item number, for the names of the goods and their quantity respectively.

Task is to amount pencils (27 pcs). So, from the above it is clear that we need to use regular expressions in the condition. The word pencil is a common fragment of text to all fields with pencils. And since we have the characters before and after the common fragment, we have to point it. The regular expression is look like ".*Pencil.*". The dot (.) means any character, the asterisk (*) means any number of characters, which go before the asterisk. So, the sequence of the point-asterisk means any number of any character. The formula for such a table look like:

=SUMIF(A2:A6; ".*pencil.*"; B2:B6)

A more advanced method is regular expression with reference to a cell in which specified keyword is (typing by hands or selected from the list). Assume that the desired value is set in cell E18:

=SUMIF(A2:A6; ".*"&E18&".*"; B2:B6)

One note. To date, there is the bug 93510, which does not allow us the use of regular expressions in conjunction with the automatic find column and row labels, if the range is made up of words. Regardless of the values in the summation range function always returns 0. Most likely, this situation arises from the fact that there are no correctly setting priorities for find labels in LibreOffice Calc. You can circumvent this bug, if an empty row is between the labels and the start values of the range, or a more elegant method, to use the merged cells for labels.

SUMIFS function – search for many conditions

In contrast to the SUMIF function, the SUMIFS function allows us to specify from one to 30 conditions. Its syntax is:

SUMIFS (Sum_Range; Condition_Range1; Condition1; ...; Condition_Range30; Condition30)

I think you see that in the range of summation range will be the values which we want to add up. The conditions range is where we will search. The summation and conditions ranges may be the same or different. The conditions is what we look for (do not forget to enclose the expression in quotation ( ""). The formula allows an excessive amount of conditions. I think, 30 ranges and conditions that are enough for all occasions. The example, which I have gave when told about SUM as an array formula above, can be rewritten as:

=SUMIFS('Spending'; 'Date'; ">="&F1; 'Date'; "<="&F2)

SUBTOTAL function – summation of visible cells or subtotals

Sometimes we need to calculate the sum selected (visible) cells by AutoFilter. The easiest way in LibreOffice Calc, to use the SUBTOTAL function. This function allows us to do much more, but in this article I will show only the summation. The rest of its functionality can be found in Help. Suppose we have a very large table, where we make a selection and we want to get the sum it. For example, we take a fairly simple option:
Of course, we can use the SUMIF, SUMIFS, or even an array function, but if we use filters, the easiest way to use the SUBTOTAL function. Its syntax is:

SUBTOTAL(Function; Range)

The Function is the number of a function for using to the range. In this case, it's 9. The range is a range to which we want to apply the function. In this case, it is a summation range. As a result, the formula is inscribed in cell E1, looks like this:


If you want to put closely the cell with SUBTOTAL under a range , you need to remember when you will filter the rows to do not turn off the flag for the cell with the SUBTOTAL function.

It is not specified in the Help, but in fact it works: when we use the SUBTOTAL function without filter, the function codes listed in the Help does not exclude hidden rows from the calculation. In order to eliminate this problem, you can add to the function code 10. That is code 9 does not exclude hidden rows, but the 109 will be. After hiding to recalculate the result you need to press Ctrl + Shift + F9.
Please note, SUBTOTAL function does not include in the calculation of the cell containing the subtotals results (the same function).
Another way to solve this problems is to use the AGGREGATE function.

AGGREGATE – summation of hidden cells and cells with errors

I have given detailed description of this function in the Help, and the recently I have contributed the additional patch with additional explanations . So, I will touch only on the issue of summation by using AGGREGATE function here.
In the last example, it was shown how to summation a range with excluding of the hidden cells, but there are cases when we want to exclude more (for example, cells with errors, SUBTOTAL function and most AGGREGATE). The AGGREGATE function has more possibilities than the SUBTOTAL. In this article we are interested in the following syntax:

AGGREGATE(Function; Option; Range)

The Function is a number of which points what function is used for the range. For the purposes of this article is number 9.
The Option is a numerical value that determines which values in the calculation should be ignored. All options are numbered from 0 to 7. The following list shows the number and a brief description of the function:

Ignore only nested SUBTOTAL and AGGREGATE functions
Ignore only hidden rows, nested SUBTOTAL and AGGREGATE functions
Ignore only errors, nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows, errors, nested SUBTOTAL and AGGREGATE functions
Ignore nothing
Ignore only hidden rows
Ignore only errors
Ignore only hidden rows and errors

The Range is the range of cells to which we want to apply the function.
As you can see, this function can be configured as you like. But there is one drawback. It can work only with hidden rows, and does not work with hidded columns. Functions and errors, it can ignore and in rows and columns.

Source and additional information

No comments :

Post a Comment