Let’s say I want to keep track of the stock of some fruits, including some red and green apples,
Fruits,Number
Apples-Green,5
Apples-Red,6
Bananas,7
Apples-Green,8
etc.
And I would like to count all apples, green and red. So I added a column saying “IF(A2=“Apples*”,A3,0)” -like I would do in Excel, using asterisk to represent any characters following the string “Apples”- and sum up that column. But the whole column was 0. Why?
You don’t mention your LibreOffice version, but with the last versions it’s possible to choose between regular expressions or wildcards, this last like in excel.
Menu/Tools/Options/LibreOffice calc/Calculate - Enable wildcars in formulas
This setting does not apply to IF.
can’t use wildcard with “IF”, but you can use it with “SUMIF” and use a “range” that only includes the one cell you want to copy the value from.
From the online manual (as at Jan 2020): Enable wildcards in formulas
Specifies that wildcards are enabled when searching and also for character string comparisons.When in Calc: This relates to the database functions, and to VLOOKUP, HLOOKUP, MATCH, AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF, SUMIFS and SEARCH.
Go to Tools → Options → LibreOffice Calc → Calculate and ensure that “Enable wildcards in formulas” is marked. Then use this formula to sum the number of apples.
=SUMIF(A2:A5;"=Apples*";B2:B5)
For this example, the spreadsheet is set up as follows.
Fruits Number
~~~~~~ ~~~~~~
Apples-Green 5
Apples-Red 6
Bananas 7
Apples-Green 8
Wildcards will not work for IF. See Calculate - LibreOffice Help.