We're currently migrating from Ask to Discourse, read the details here

Ask Your Question

Using an OR function/operator in a COUNTIFS? [closed]

asked 2018-07-30 20:35:52 +0200

libreoffiguess gravatar image

updated 2020-09-08 15:24:12 +0200

Alex Kemp gravatar image

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:


I would prefer something along the lines of:


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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by libreoffiguess
close date 2018-07-30 23:04:26.114943

2 Answers

Sort by » oldest newest most voted

answered 2018-07-30 22:01:57 +0200

=COUNTIFS($I$14:$I100;">=2018-1-1";$I$14:$I100;"<=2018-12-31";$H$14:$H100;"Website|Email") works fine here with (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?

edit flag offensive delete link more


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!

libreoffiguess gravatar imagelibreoffiguess ( 2018-07-30 23:04:09 +0200 )edit

answered 2018-07-30 21:58:34 +0200

Lupp gravatar image

updated 2020-08-31 12:16:04 +0200

May I suggest first of all that you read https://ask.libreoffice.org/en/questi... ?

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

edit flag offensive delete link more


Number 1 did it. Thank you!

libreoffiguess gravatar imagelibreoffiguess ( 2018-07-30 23:05:09 +0200 )edit

Question Tools

1 follower


Asked: 2018-07-30 20:35:52 +0200

Seen: 2,116 times

Last updated: Aug 31 '20