A Guide to LibreOffice Calc’s IF Functions

LibreOffice is a freeware office suite that includes the Calc spreadsheet application. Calc is a software package that has plenty of functions and formulas for spreadsheets. A few of those are conditional functions that give you formula results and values based on a specific condition. This is how to add some of Calc’s conditional IF functions to spreadsheets.

If you don’t already have this application, click the Download Version 5.2.0 button on this page. Run through the LibreOffice setup wizard to install the suite. Then open the Calc window shown in the snapshot below.

The IF Function

First, let’s add a basic IF/ELSE function to a Calc spreadsheet. This enables you to set up a conditional statement whereby the result of one cell depends on another cell’s value. First, enter the value 777 in cell B4. Then click cell C4 and press the Function Wizard button. Select IF from that window, and then click the Next button to open the function options shown directly below.

Click the Select button beside the Test text box, and then select the B4 cell. Next, enter >500 after B4 in the Test text box. Enter “true” in the Then_value box, and input “false” in the Otherwise_value text box as shown in the snapshot below.

Click OK to close the window. The spreadsheet will now match the one shown in the shot directly below. What you’ve just done here is set up a conditional if function that confirms the value of cell B4 is higher than 500. If the B4 number was lower than 500, the IF cell would include false. The full formula is =IF(B4 > 500,”true”,”false”). 

You can set up a variety of IF functions much the same with =, > and < that mean equal to, more than or less than. To include a numerical value in the IF cell, enter the number or a cell reference in the Then_value box without any extra quotation marks. The quote marks are required for text output as in the example.

The SUMIF function

There are numerous functions that extend upon the basic IF statement. For example, with the SUMIF function you can add numbers together that match a specific criteria. For example, let’s suppose you only needed to sum sales figures that match a specific criteria, or condition, on a spreadsheet. Then SUMIF, or SUMIFS for multiple conditions, would be ideal for that.

As an example, set up a SUMIF function that only adds cells together in a range that eclipse a certain value. To do that, input four values into the spreadsheet exactly as shown directly below. Then select a cell to include the SUMIF function, and press the Function Wizard button. Select SUMIF and click the Next button to open the SUMIF wizard.

Click the Select button beside range text box, and then select the cells that include the numbers you entered. Below that you should enter “>55” in the criteria box. You should also select the same cells B4:B7 in the sum_range box as below.

Now when you click the OK button, the spreadsheet will return a value of 154 in the SUMIF cell. Thus, the spreadsheet has added the two cells together with numbers higher than 55. The two cells with 77 in them amount to 154.

So you can add up numbers in a column or row less than or equal to a specific value much the same. For that you would need to replace the > in the criteria box with either < or =. For example, to add numbers less than 55 you would input “<55” in the criteria field.

The COUNTIF function

COUNTIF is another conditional function you can add to Calc spreadsheets. This function adds up the number of cells, not their specific values, that match a condition. For example, you could set up a COUNTIF function that counts how many cells in a column include numbers that are less than a specific value.

So let’s do that by entering some numbers into a Calc spreadsheet exactly as in the snapshot directly below. Click a cell to add the COUNTIF function to, and then open the Function Wizard. Select COUNTIF > Next to open its wizard.

Select the numbers entered into the spreadsheet by clicking the Select button beside range. Type “=1232” in the criteria box as shown directly below. Close the Function Wizard window.

Now the COUNTIF cell will tally the number of cells that include 1,232 in them, which in this example amounts to three. You can tally up how many of the cells include a higher or lower value than 1,232 by replacing the = with < or >. This function could come in handy for larger spreadsheets with lots of numbers in a column or row.

The AVERAGEIF function

The AVERAGEIF function is similar to SUMIF except it finds the average value of cells based on a specific condition. So you could find the average value of cells that eclipse or are lower than a specific number. Alternatively, you could also base the condition on a row or column heading.

Enter a few numbers into a spreadsheet row exactly as in the snapshot directly below. Select a cell for the AVERAGEIF function, open the Function Wizard and select AVERAGEIF. That will open the AVERAGEIF wizard to set up the function with.

Press the Select button beside the range box to select the cells you entered numbers into. Enter “<145” in the criteria box. Select the same cells as the range box for the average_range box. Click OK to close the window.

Now the AVERAGEIF cell should have a value of 131. That’s the average of the two cells values in the column lower than 145. The 139 and 123 values amount to 162 that divides by two to equal 131.

You can also set up a condition based on text in another column or row. For example, enter some text into an adjacent column on the spreadsheet as below. Then select the cells in the row that includes the text for the AVERAGEIF function’s range box. Enter “spring” in the criteria box, and select the cells with the numbers in them for the average_range text box. That will find the average of the cell values in the spring rows.

Those are four of the conditional functions you can add to your Calc spreadsheet. You can also select SUMIFS, COUNTIFS and AVERAGEIFS functions to set up functions based on multiple conditions. The functions will certainly come in handy when you need some values from data table cells that match specified conditions.