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.