How can I rewrite an Excel formula to exclude users with an @mydomain.com in their email address?

0
votes

How can I rewrite this Excel formula so it properly excludes users with @mydomain.com in their email address? The formula currently counts all unique logins. The formula needs to accommodate blank records between rows 2 and 100,000.

Data sheet name "Data-User":
Column A is the activity type
Column C contains the date
Column M contains the user's email address
Column O contains the username

In another sheet (the report):
B6 is the cell containing the start date of the report
C6 is the cell containing the end date of the report

=SUM(IF(FREQUENCY(

IF(('Data-User'!A2:A100000="ACTIVITY_LOGIN_USER")*
('Data-User'!C2:C100000>=B6)*('Data-User'!C2:C100000<=C6)*
('Data-User'!O2:O100000<>"admin")*
('Data-User'!M2:M100000<>"*@mydomain.com")>0,
MATCH('Data-User'!M2:M100000,'Data-User'!M2:M100000,0),""),

IF(('Data-User'!A2:A100000="ACTIVITY_LOGIN_USER")*
('Data-User'!C2:C100000>=B6)*('Data-User'!C2:C100000<=C6)*
('Data-User'!O2:O100000<>"admin")*
('Data-User'!M2:M100000<>"*@mydomain.com")>0,
MATCH('Data-User'!M2:M100000,'Data-User'!M2:M100000,0),"")

)>0,1))
1
excelexcel-formula

1 Answers

0
votes

I think all you need to do is replace the part where you compare M2:M100000 with mydomain.com with

ISERROR(SEARCH("@mydomain.com",'Data-User'!M2:M100000))

so if it doesn't find @mydomain.com in the email address it will be included in the count.

However this could fall over if the email address is missing, so need to add

len('Data-User'!M2:M100000)>0

as well.

So the whole formula would be

=SUM(IF(FREQUENCY(

IF(('Data-User'!A2:A100000="ACTIVITY_LOGIN_USER")*
('Data-User'!C2:C100000>=B6)*('Data-User'!C2:C100000<=C6)*
('Data-User'!O2:O100000<>"admin")*
ISERROR(SEARCH("@mydomain.com",'Data-User'!M2:M100000))*
(LEN('Data-User'!M2:M100000)>0) >0,
MATCH('Data-User'!M2:M100000,'Data-User'!M2:M100000,0),""),

IF(('Data-User'!A2:A100000="ACTIVITY_LOGIN_USER")*
('Data-User'!C2:C100000>=B6)*('Data-User'!C2:C100000<=C6)*
('Data-User'!O2:O100000<>"admin")*
ISERROR(SEARCH("@mydomain.com",'Data-User'!M2:M100000))*
(LEN('Data-User'!M2:M100000)>0) >0,
MATCH('Data-User'!M2:M100000,'Data-User'!M2:M100000,0),"")

)>0,1))