Between 2 Dates - Adding to my existing formula.

That is not a long formula by any stretch of the imagination!!! :D

I need to adapt this formula.

The existing formula I am using performs a count of unique user ID numbers in column A (excluding duplicates) if the date in column J contains a user profile expiry date is within 180 days of age.


=SUM(--(FREQUENCY(IF(Sheet1!A:A<>"",IF(Sheet1!J:J>TODAY()-180,MATCH(Sheet1!A:A,Sheet1!A:A,0))),ROW(Sheet1!A:A)-ROW(Sheet1!A2)+1)>0))


I'll provide a visual illustration of my scenario. As you can see I have 3 users (see User ID column A). I need a formula which performs a total count of User ID (excluding duplicates) by looking at the most recent login date only and checking if it's within the last 180 days regardless of the values in column R.

So the result of the correct formula based on the table below would calculate that only 2 user ID's have logged in within the last 180 days.


Column A Column J Column R
User ID Last Login Date User Role
123 05/11/2018 Standard
123 01/04/2018 Super
123 02/04/2016 Test
456 05/08/2018 Super
456 01/11/2018 Test
789 09/09/2017 Test
789 23/01/2017 Super

I believe that the formula (if array entered*) works. Given that this question was posted on Nov. 6th 2018 the only date that would have been within the past 180 days was 5/11/2018, (5/8/2018 was 182 days in the past) so the formula should, and does, return 1.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

It may help us, in our effort to help you, if you could upload a sample showing when the formula fails to return the correct value.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

As requested I've attached an excel file.

The formula is on sheet 1. Raw data is on sheet 2.

As you can see from sheet 2, there are 3 occurances (by 2 users) of user(s) who logged in during the last 180 days.


123 05/11/2018 Standard
456 05/08/2018 Super
456 01/11/2018 Test

The formula returns a value of 3. I would like it to return a value of 2 since there are only 2 users who logged in during the last 180 days.

I was not able to evaluate the formula due to the whole column references, however when I used the range 2:10 (as modeled in cell B2) I got the expected result of 2.
As you may need the range on sheet 2 to be expandable you might consider converting to a table and using structured references (as modeled in B3) for your formula.
Range formula*:

Formula:

Please Login or Register  to view this content.

Structured reference formula*:

Formula:

Please Login or Register  to view this content.

*Denotes an array entered formula.
Let us know if you have any questions.

You're Welcome and glad that you got a solution that works for you. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.