COUNTS Given Criteria

[erahi]

I have data that shows the all the store visits made by each person with their timestamps.

In Column A I have the Person ID and column B is the timestamp of the visit (e.g. 23/4/2017)​

​

<strike></strike>

I need a formula that will work out the following in two separate columns:

- A person who has made at least 1 visit in a 6 month period
- A person who has made 6 or more visits in a 3 month period

I'm guessing I would need a COUNTIFS with an IF formula....​

 

[RasGhul]

Hi erahi,


I've made Start Date cells in F2 & G2 so you can control when your 3 or 6 month period starts, then enter and drag the Countifs down your ID list Column C as per below;

In D1

=EOMONTH(F2,2)

*This adds the last date 3 months after your start date "F2"

In E1

=EOMONTH(G2,5)

*This adds the last date 6 months after your start date "G2"

In the 3 month Column D

=IF(COUNTIFS($A$2:$A$29,C2,$B$2:$B$29,">="&$F$2,$B$2:$B$29,"<="&$D$1)>=6,COUNTIFS($A$2:$A$29,C2,$B$2:$B$29,">="&$F$2,$B$2:$B$29,"<="&$D$1),"")

In the 6 month Column E

==IF(COUNTIFS($A$2:$A$29,C3,$B$2:$B$29,">="&$G$2,$B$2:$B$29,"<="&$E$1)>1,COUNTIFS($A$2:$A$29,C3,$B$2:$B$29,">="&$G$2,$B$2:$B$29,"<="&$E$1),"")

 

[erahi]

Hello, this isnt quite what I was after. The problem is that the start date and end date are not fixed - it could be any date. I need to know which person has made 6+ visits within any 3 month period

I realised this is quite a complicated calculation.

 

[RasGhul]

So True or false is ok for each ID?