Libreoffice Calc 6.4.7.2 on Linux Mint 20.3
I have data in 2 columns, column A is a text activity code, and B the number of minutes spent indulging in that activity. There are at least 6 different values for the activity code.
I want to code a SUMIF to give the total time spent doing any activity OTHER than one value,
i.e. include values in column B if column A is A,B,C,E,F and exclude values where column A = D.
I would prefer not to sum all of the values then subtract those matching D.
Is there a way I have failed to find to do this?
why not? Its pretty simple to do: =SUM(B:B)-SUMIF(A:A;"D";B:B)
a bit more sophisticated is: =SUMIF(A:A;"A|B|C|E|F";B:B) with →Tools→Options→Calc→calculate→ [x]allow regular Expressions in Formulas
but I would prefer: →→Data→Pivottable→create…
… Or use the documentation on “criterion” used in SUMIF:
A matching expression can be:
…
- A value beginning with a comparator (<, <=, =, >, >=, <>).
…
For <>, if the value is not empty it matches any cell content except the value, including empty cells.
So:
=SUMIF(A:A;"<>D";B:B)
Note tdf#131995. New versions not only improve the program, but also the documentation.
I saw the ‘<>’ in the documentation, but I could not get it to work. I (incorrectly!) presumed ‘<>’ to only be relevant to numeric values. The real use case is a much more complex calculation involving multiple criteria in a SUMIFS statement. I was actually trying to negate the content of a cell in another sheet, and not a simple text value in the formula. I have it working now, not quite how I want it, but it’ll do for now while I work on solving another problem.
Thank you all for your efforts.