Ask Your Question
0

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

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

libreoffiguess 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:

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

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
1

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

edit flag offensive delete link more

Comments

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
1

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

Lupp gravatar image

updated 2018-07-30 22:20:35 +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.)

-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

Comments

Number 1 did it. Thank you!

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

Question Tools

1 follower

Stats

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

Seen: 363 times

Last updated: Jul 30 '18