How to return unique values using COUNTIFS ?

This is a working formula, except it returns more than one login instance for a user. For example, if jdoe logged in multiple times between May 1 and May 20, it would count each login. The goal is to just count one of jdoe's logins... same deal for other users. How can I do this?

Current formula:

=COUNTIFS('Data-User'!$A$2:$A$500000,"ACTIVITY_LOGIN_USER",'Data-User'!$C$2:$C$500000,">="&B6,'Data-User'!$C$2:$C$500000,"<="&C6,'Data-User'!$O$2:$O$500000,"<>admin",'Data-Content'!$M$2:$M$500000,"<>*@mycompanydomain.com")

Explanation of current formula:

Column "O" contains usernames.

Formula Segment	Comments
=COUNTIFS('Data-User'!$A$2:$A$500000,"ACTIVITY_LOGIN_USER",	This just looks at the activity type (in this case, a login event).
'Data-User'!$C$2:$C$500000,">="&B6,'Data-User'!$C$2:$C$500000,"<="&C6,	Date range to consider.
'Data-User'!$O$2:$O$500000,"<>admin",	Do not count system admin logins.
'Data-Content'!$M$2:$M$500000,"<>*@mycompanydomain.com")	Do not count logins from my company's staff.

Hi,

Let me know if you were thinking of something more complex, but from what I understand all you have to do is "=if(YourLongFormula>0, True, False)". I realize this answer seems overly simple, if I misunderstood what you are trying to accomplish let me know. For what its worth, I'd probably keep the long formula you have above in a separate cell from the other if statement just to keep things more clear for the reader.

Currently, the formula counts the number of times people login in a given period of time (i.e. from June 1, 2015 to June 30, 2015), while not considering admin users or company staff users (based on email address). The value returned in test data is 1,793. However, when I took the same data and manually filtered against the login type and removed duplicate user logins, the number was much lower (394).

I feel as if I need to filter within the COUNTIFS formula somehow, or rewrite my formula not using COUNTIFS. Any pointers would be appreciated.

I have also tried these options, but it is not returning a numerical count value:
IF(max(your formula)>0,1,0)
=IF(YourLongFormula>0, True, False)

Have you tried adding another column in the data that determines if the login is unique? Its hard for me to write the formula for it without seeing the data, but you would just have to have an if statement that checks if the current value exists above the current row. Then have a formula that sums all the ones in this column.

Ignore my previous reply as I misunderstood your problem.

The formula to see if the value is unique in the data set would be something like =IF(COUNTIF($D$2:D2,"="&D3)>0,0,1). This formula is in cell e3. Returns 1 if unique.

Thanks for all of your suggestions. I am not sure if I was explaining my issue as best I could so I put an example together showing the problem in context (attachment). There is a slightly modified formula in that document that drops one of the COUNTIFS exclusions for this example, and I also limited the data set to ~40 rows. The issue is highlighted in light yellow/red text.

Thanks again,
Curtis

Array formula* in Report B10...

Please Login or Register  to view this content.

*Confirm cell entry with Ctrl+Shift+Enter

Aha! Learned something myself trying to resolve your issue...

First, the portion...

Please Login or Register  to view this content.

...will have no matches because there are no email addresses with that domain in you example file. The only similar domain is @mydomain.com.

Next, even changing to...

Please Login or Register  to view this content.

...had no effect.

Apparently, Excel does not see the * as a wildcard in that particular portion. I had to change it to...

Please Login or Register  to view this content.

Note that is at both locations in the formula.

@ccarmichael

My previous reply was to your last post before you edited (I had left it sitting on my desktop since yesterday).

To answer your edited version, should be simply a matter of changing the "=" to "<>" (taking my last post details into consideration also).

Thanks for your reply. Just to confirm, is this what you had for the full formula?

Please Login or Register  to view this content.

Looks about right... but don't recall specifically. You're asking me about 3 days too late.

Thanks both. If I wanted to track "New Visitors", I would want to consider a create user activity type, and a login user activity type. How could I add a second activity type into the formula? Again, to track only unique/one time logins in a given date range. I have put in bold the second activity type I would like to consider, although that does not produce accurate results.

=SUM(IF(FREQUENCY(IF(('Data-User'!A2:A210951="ACTIVITY_LOGIN_USER"&"ACTIVITY_CREATE_USER")*('Data-User'!C2:C210951>=B6)*('Data-User'!C2:C210951<=C6)*('Data-User'!O2:O210951<>"admin")*(RIGHT('Data-User'!M2:M210951,13)<>"@mydomain.com"),MATCH('Data-User'!H2:H210951,'Data-User'!H2:H210951,0)),ROW('Data-User'!A2:A210951)-ROW('Data-User'!A2)+1),1))

@jhren,

I've been doing some new QA work on my report and was looking at the formula you suggested. Just wanted to clarify what the formula is doing with column H? This is newly surfaced in the formula you suggested and maps to the full name field so just trying to best understand your recommendation.

Regards,
Curtis

Tha MATCH function uses column H as the lookup value and lookup array. Being an array formula, it checks each cell of the lookup array for a match, and it returns the row index of the first match. This allows the FREQUENCY function to determine if there is duplicate entries in column H.

Re your post before last, you'd have to use a [part] statement such as:
...IF((('Data-User'!A2:A210951="ACTIVITY_LOGIN_USER")+('Data-User'!A2:A210951="ACTIVITY_CREATE_USER"))*...
Returns a 1 if either value; 0 otherwise.