Using an OR function/operator in a COUNTIFS?

[libreoffiguess]

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, 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.

[Lupp]

May I suggest first of all that you read Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? ?

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 ?

[libreoffiguess]

Number 1 did it. Thank you!

[mikekaganski]

=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?

[libreoffiguess]

Weird, 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!