Forum Discussion
Advanced Filters, is it possible colA=("a" OR "b" OR "c") AND colB=("1" OR "2" OR "3") on one row?
Doing some diagnostics of mail delivery failures of messages within several domains all under the same Office 365 tenant.
So, looking for messages with domains a.com, b.com, and c.com as part of both the SenderAddress and RecipientAddress this requires a criteria table nine rows tall as in:
| SenderAddress | RecipientAddress |
| =*a.com | =*a.com |
| =*a.com | =*b.com |
| =*a.com | =*c.com |
| =*b.com | =*a.com |
| =*b.com | =*b.com |
| =*b.com | =*c.com |
| and so on |
We have nine domains of interest which results in a criteria table 81 rows tall!! Not so easy to read the intent or be sure all domain pairs are identified. I've searched in vain for a way to do something like the following. Can anyone point me in the direction of a solution like this?
| SenderAddress | RecipientAddress |
| =*a.com OR =*b.com OR =*c.com | =*a.com OR =*b.com OR =*c.com |
Thank you.
Let the data be in A:B with headers in A1:B1 and the criteria range I1:I2 with header left blank and this formula in I2:
=SUMPRODUCT(COUNTIFS(A2,{"*@a.com","*@b.com","*@c.com"}))+SUMPRODUCT(COUNTIFS(B2,{"*@a.com","*@b.com","*@c.com"}))=2Sorry never got back. I was new here and lost the link.
Your solution looks like an array(?) formula since it has the curly braces {}. I've always struggled to understand them but will definitely give it a try.
Thank you.
The curly braces are used inside the formula and indicate an array syntax. It can replaced with a cell range.
And yet the formula is an array formula because COUNTIFS() expects a single value for the criteria and my formula passes a set of criteria.
