Ask Your Question
0

If cell contains text, then... [closed]

asked 2017-10-01 10:51:02 +0200

Artie gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-16 16:15:24.206640

2 Answers

Sort by » oldest newest most voted
0

answered 2017-10-02 17:36:42 +0200

Jim K gravatar image

updated 2017-10-02 17:42:18 +0200

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 https://help.libreoffice.org/Common/C....

edit flag offensive delete link more
0

answered 2017-10-01 12:30:30 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more

Comments

This setting does not apply to IF.

Jim K gravatar imageJim K ( 2017-10-02 17:30:50 +0200 )edit

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.

zx81 gravatar imagezx81 ( 2020-01-16 05:54:24 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2017-10-01 10:51:02 +0200

Seen: 40,351 times

Last updated: Oct 02 '17