Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 30 Jul 2018 23:05:09 +0200Using an OR function/operator in a COUNTIFS?https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/ Is it at all possible to include an OR function or alternation as part of the criterion of a COUNTIFS function, and if so, how is it done?
I want to count the number of times an entry/row within a certain date range, and with one of two specific text values, appears in a given table. I did try using SUMPRODUCT instead, but couldn't get it to cooperate and it was harder to read. Currently the formula that I'm using to give me the number I want is:
=SUM(COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"Website"),COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"Email"))
I would prefer something along the lines of:
=SUM(COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"(Website|Email)"))
But nothing I input seems to work. [The wiki states that COUNTIFS takes regular expressions](https://help.libreoffice.org/Calc/COUNTIFS_function#Syntax), and `"Web*"` will return a count for all entries with "Website" just fine, but for some reason the `|` regex operator doesn't work. (`||`, and `"Website|Email"` without brackets also don't work.) I also can't use OR() since there doesn't appear to be any way to reference the COUNTIFS criteria within the criterion (attempting `OR(H14="Website",H14="Email")` doesn't appear to work at all; even when H14 does contain either "Website" or "Email", the resulting count is 0).
The example I've used may look pretty minor as a use case, but I'm going to be SUMing several more of these together across sheets and would prefer to eliminate the need to double up on functions.Mon, 30 Jul 2018 20:35:52 +0200https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/Answer by Lupp for <p>Is it at all possible to include an OR function or alternation as part of the criterion of a COUNTIFS function, and if so, how is it done?</p>
<p>I want to count the number of times an entry/row within a certain date range, and with one of two specific text values, appears in a given table. I did try using SUMPRODUCT instead, but couldn't get it to cooperate and it was harder to read. Currently the formula that I'm using to give me the number I want is:</p>
<pre><code>=SUM(COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"Website"),COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"Email"))
</code></pre>
<p>I would prefer something along the lines of:</p>
<pre><code>=SUM(COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"(Website|Email)"))
</code></pre>
<p>But nothing I input seems to work. <a href="https://help.libreoffice.org/Calc/COUNTIFS_function#Syntax">The wiki states that COUNTIFS takes regular expressions</a>, and <code>"Web*"</code> will return a count for all entries with "Website" just fine, but for some reason the <code>|</code> regex operator doesn't work. (<code>||</code>, and <code>"Website|Email"</code> without brackets also don't work.) I also can't use OR() since there doesn't appear to be any way to reference the COUNTIFS criteria within the criterion (attempting <code>OR(H14="Website",H14="Email")</code> doesn't appear to work at all; even when H14 does contain either "Website" or "Email", the resulting count is 0).</p>
<p>The example I've used may look pretty minor as a use case, but I'm going to be SUMing several more of these together across sheets and would prefer to eliminate the need to double up on functions.</p>
https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?answer=161711#post-id-161711May I suggest first of all that you read https://ask.libreoffice.org/en/question/131311/ ?
Can you upload an example demonstrating your problem?
Are the contents or results in your column `I` numbers or strings?
-1- Are you sure you enabled `Tools > Options > LibreOffice Calc > Enable regular expressions in formulae`? Without that NO formula can actually interpret RegEx. (The parentheses are **necessary** if you want to define an alternative.)
[Edit time=2020-08-31 about 10:10 UTC]
(From -1-) "Without that NO formula can actually interpret RegEx." is no longer *exactly* correct. The new function REGEX(), implemented first with LibO version 6.2, always works, of course, with RegEx, independent of the mentioned setting.
[/Edit]
-2- Always use YYYY-MM-DD if Dates shall also be comparable if given as texts. I am not sure if the otherwise necessary conversions are reliebale.
-3- Did you consider to use SUMPRODUCT() for your purpose? It might relief you of some ticklish issues occurrung with the "Criteria" toy. (Automatic conversions again.)
-4- What shall the SUM() in your forula be goog for?
-5- In an as-if range like `$I14:$I`: How should Calc decide if you mean `$I$1 or $I$1048576` by the single `$I` ?Mon, 30 Jul 2018 21:58:34 +0200https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?answer=161711#post-id-161711Comment by libreoffiguess for <p>May I suggest first of all that you read <a href="https://ask.libreoffice.org/en/question/131311/">https://ask.libreoffice.org/en/questi...</a> ?</p>
<p>Can you upload an example demonstrating your problem? <br>
Are the contents or results in your column <code>I</code> numbers or strings? </p>
<p>-1- Are you sure you enabled <code>Tools > Options > LibreOffice Calc > Enable regular expressions in formulae</code>? Without that NO formula can actually interpret RegEx. (The parentheses are <strong>necessary</strong> if you want to define an alternative.) <br>
[Edit time=2020-08-31 about 10:10 UTC] <br>
(From -1-) "Without that NO formula can actually interpret RegEx." is no longer <em>exactly</em> correct. The new function REGEX(), implemented first with LibO version 6.2, always works, of course, with RegEx, independent of the mentioned setting. <br>
[/Edit] <br>
-2- Always use YYYY-MM-DD if Dates shall also be comparable if given as texts. I am not sure if the otherwise necessary conversions are reliebale. </p>
<p>-3- Did you consider to use SUMPRODUCT() for your purpose? It might relief you of some ticklish issues occurrung with the "Criteria" toy. (Automatic conversions again.) </p>
<p>-4- What shall the SUM() in your forula be goog for? </p>
<p>-5- In an as-if range like <code>$I14:$I</code>: How should Calc decide if you mean <code>$I$1 or $I$1048576</code> by the single <code>$I</code> ?</p>
https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?comment=161716#post-id-161716Number 1 did it. Thank you!Mon, 30 Jul 2018 23:05:09 +0200https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?comment=161716#post-id-161716Answer by Mike Kaganski for <p>Is it at all possible to include an OR function or alternation as part of the criterion of a COUNTIFS function, and if so, how is it done?</p>
<p>I want to count the number of times an entry/row within a certain date range, and with one of two specific text values, appears in a given table. I did try using SUMPRODUCT instead, but couldn't get it to cooperate and it was harder to read. Currently the formula that I'm using to give me the number I want is:</p>
<pre><code>=SUM(COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"Website"),COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"Email"))
</code></pre>
<p>I would prefer something along the lines of:</p>
<pre><code>=SUM(COUNTIFS($I$14:$I,">=2018-1-1",$I$14:$I,"<=2018-12-31",$H$14:$H,"(Website|Email)"))
</code></pre>
<p>But nothing I input seems to work. <a href="https://help.libreoffice.org/Calc/COUNTIFS_function#Syntax">The wiki states that COUNTIFS takes regular expressions</a>, and <code>"Web*"</code> will return a count for all entries with "Website" just fine, but for some reason the <code>|</code> regex operator doesn't work. (<code>||</code>, and <code>"Website|Email"</code> without brackets also don't work.) I also can't use OR() since there doesn't appear to be any way to reference the COUNTIFS criteria within the criterion (attempting <code>OR(H14="Website",H14="Email")</code> doesn't appear to work at all; even when H14 does contain either "Website" or "Email", the resulting count is 0).</p>
<p>The example I've used may look pretty minor as a use case, but I'm going to be SUMing several more of these together across sheets and would prefer to eliminate the need to double up on functions.</p>
https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?answer=161712#post-id-161712`=COUNTIFS($I$14:$I100;">=2018-1-1";$I$14:$I100;"<=2018-12-31";$H$14:$H100;"Website|Email")` works fine here with 6.1.0.2 (given that Options-LibreOffice Calc-Calculate-Enable regular expressions in formulas is enabled - wildcards are the default in Excel types since 5.2).
Rather, the `$I$14:$I` syntax doesn't work here - are you sure it works for you?Mon, 30 Jul 2018 22:01:57 +0200https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?answer=161712#post-id-161712Comment by libreoffiguess for <p><code>=COUNTIFS($I$14:$I100;">=2018-1-1";$I$14:$I100;"<=2018-12-31";$H$14:$H100;"Website|Email")</code> works fine here with 6.1.0.2 (given that Options-LibreOffice Calc-Calculate-Enable regular expressions in formulas is enabled - wildcards are the default in Excel types since 5.2).</p>
<p>Rather, the <code>$I$14:$I</code> syntax doesn't work here - are you sure it works for you?</p>
https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?comment=161715#post-id-161715Weird, I swore that indefinite range end was working before; maybe I got it confused with Google Sheets functionality.
In any case enabling the regular expressions option worked! I didn't even know that was something that could be turned off in the first place. Thanks!Mon, 30 Jul 2018 23:04:09 +0200https://ask.libreoffice.org/en/question/161709/using-an-or-functionoperator-in-a-countifs/?comment=161715#post-id-161715