How to count Monday and Friday as one occurance

Hi,
Below is my sample data set. Question: How many consequtive Fridays and Mondays did Chris W take in the data set below? The answer is 4.

Criteria 1. Look at the Days column: Does Monday follow after Friday? If it does than count it, if it does not, don't count it.
Criteria 2. Is there a separation of 2 days between Friday and Monday? If yes,then count it, if there is more than 2 days, don’t count it.

Please see let me know if you could help me with this. I have also attached my sample spreadsheet. Thank you.

Data Sample:
Personnel Date Month Days
Chris W 5/10/2013 May Fri
Chris W 6/6/2013 Jun Thu
Chris W 6/13/2013 Jun Thu
Chris W 6/14/2013 Jun Fri
Chris W 6/27/2013 Jun Thu
Chris W 6/28/2013 Jun Fri
Chris W 7/12/2013 Jul Fri
Chris W 8/16/2013 Aug Fri
Chris W 8/19/2013 Aug Mon
Chris W 8/20/2013 Aug Tue
Chris W 8/30/2013 Aug Fri
Chris W 9/6/2013 Sep Fri
Chris W 9/20/2013 Sep Fri
Chris W 9/23/2013 Sep Mon
Chris W 9/27/2013 Sep Fri
Chris W 9/30/2013 Sep Mon
Chris W 10/25/2013 Oct Fri
Chris W 10/28/2013 Oct Mon

Hi Daffodil11, thank you very much for your reply! I've done exactly how you've done it ...for Chris W i am getting 5 ( should be 4) and for Mark C i am getting 2 and the result should be 1. What i am noticing for Chris W (the very last row for Chris W data) that there is one Friday that is after Monday that is being counted (the sequence should be Mon-Fri) and also that there are two different months (the formula should capture the same month data cuz the difference in days cant be more than 2). I hope this makes sense. Thank you very much!!
Sample data
Personnel Number Date Month Days True/false
Chris W 5/10/2013 May Fri 0
Chris W 6/6/2013 Jun Thu 0
Chris W 6/13/2013 Jun Thu 0
Chris W 6/14/2013 Jun Fri 0
Chris W 6/27/2013 Jun Thu 0
Chris W 6/28/2013 Jun Fri 0
Chris W 7/12/2013 Jul Fri 0
Chris W 8/16/2013 Aug Fri 1
Chris W 8/19/2013 Aug Mon 0
Chris W 8/20/2013 Aug Tue 0
Chris W 8/30/2013 Aug Fri 0
Chris W 9/6/2013 Sep Fri 0
Chris W 9/20/2013 Sep Fri 1
Chris W 9/23/2013 Sep Mon 0
Chris W 9/27/2013 Sep Fri 1
Chris W 9/30/2013 Sep Mon 0
Chris W 10/25/2013 Oct Fri 1
Chris W 10/28/2013 Oct Mon 0
Chris W 12/13/2013 Dec Fri 1
Mark C 2/15/2013 Feb Fri 0
Mark C 3/28/2013 Mar Thu 0
Mark C 4/1/2013 Apr Mon 0
Mark C 5/3/2013 May Fri 0
Mark C 5/17/2013 May Fri 0
Mark C 6/14/2013 Jun Fri 0
Mark C 6/28/2013 Jun Fri 0
Mark C 7/12/2013 Jul Fri 0
Mark C 7/19/2013 Jul Fri 0
Mark C 7/26/2013 Jul Fri 1
Mark C 7/29/2013 Jul Mon 0
Mark C 8/23/2013 Aug Fri 0
Mark C 10/11/2013 Oct Fri 1
Mark C 12/30/2013 Dec Mon 0
Mark C 12/31/2013 Dec Tue 0

Originally Posted by bebo021999

Try criteria 1:
=SUMPRODUCT(--(($D$3:$D$19="Mon")*2-($D$2:$D$18="Fri")>0))

Criterial 2:
=SUMPRODUCT(--(($D$5:$D$19="Mon")*2-($D$2:$D$16="Fri")>0))

Thank you very much Bebo0219999!

In my summary table i am trying to sum up the results for the people on my list, in this case Chris W and Mark C so i dont have to copy paste your formula every time for these individuals ( i have about 500 people in the actual database)
I tried =SUMPRODUCT(($A$3:$A$36=$M3),--(($D$3:$D$36="Mon")*2-($D$2:$D$35="Fri")>0)) but it doesnt work ....If you could please let me know how you would do it that would be great! Thank you again!

Thank you daffodil11 so much!

Hi Bebo021999,

thank you very much for sending me the Sumproduct formulas for the two creterias below. The formula (=SUMPRODUCT(--((IF($A$4:$A$36=$K3,$D$4:$D$36,"")="Mon")*2-(IF($A$3:$A$35=$K3,$D$3:$D$35,"")="Fri")=1)) is working great when there is Mon after Fri. The problem happens when there are 2 or 3 Mondays after Friday. Then the formula gets tricked into counting these extra mondays. Let me give you an example:
8/9/2013 Aug Fri
8/12/2013 Aug Mon
8/19/2013 Aug Mon
8/26/2013 Aug Mon
So the formula counts this as 3 occurances but it should be only 1. Similar is happening with Fridays. If you could help me with this that would be great. If you want i can send you my spreadsheet
Criteria 1. Look at the Days column: Does Monday follow after Friday. If yes than count it, if not don’t count.
Criteria 2. Is there a separation of 2 days between Friday and Monday? If yes count it, if there is more than 2 days, don’t count

Thank you,
Miro

Hi bebo021999, i tried to attach a sample file so you can see what is going on but i cant. I will open up another thread.

Hi bebo021999, i was able to upload the file so you can see what is going on. I am looking at the formula 3 at moment. On the assumptions tab the formulas are defined. I also struggle with the formula 2 and the formula 1. I have included the manual count as well so you can see the logic and inserted my comments. Any help would be greatly appreciated. Thank you.