+ Reply to Thread
Results 1 to 12 of 12

How to count Monday and Friday as one occurance

  1. #1
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    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
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: How to count Monday and Friday as one occurance

    E3:

    =IF(AND(WEEKDAY(B3)=6,SUMPRODUCT(--(B4:B6-B3<=4))),1,0)

    and copy down. This will mark each occurence.

    Chris's Total is: =SUMIF(a3:A36,"chris w",e3:e36)




    You can substitute the 1 for "Fri/Mon Off" or whatever you need.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,037

    Re: How to count Monday and Friday as one occurance

    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))

  4. #4
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to count Monday and Friday as one occurance

    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

  5. #5
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to count Monday and Friday as one occurance

    Quote Originally Posted by bebo021999 View Post
    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!

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: How to count Monday and Friday as one occurance

    Ahh yes, I see it now.

    E3:

    =IF(AND(WEEKDAY(B3)=6,SUMPRODUCT((B4:B6-B3<=4)*(A4:A6=A3))),1,0)

    I added a layer to the SUMPRODUCT, so that it only counts rows where the name matches.

    6215 48Street SE.xlsx

  7. #7
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to count Monday and Friday as one occurance

    Thank you daffodil11 so much!

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,037

    Re: How to count Monday and Friday as one occurance

    OK, try again
    criteria 1
    Please Login or Register  to view this content.
    criteria 2
    Please Login or Register  to view this content.
    Both are array formula then confirmed by Ctrl-shift-enter rather than enter only.

  9. #9
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to count Monday and Friday as one occurance

    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

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,037

    Re: How to count Monday and Friday as one occurance

    I am so supprise that it does count 3 occurances, be cause it works in pairs: D4-D3, D5-D4,D6-D5...returns 1*2-1=1 where pair of Mon-Fri founds.
    Try to upload updated file with unexpected reults displayed.

  11. #11
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to count Monday and Friday as one occurance

    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.

  12. #12
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to count Monday and Friday as one occurance

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count Holidays within Month, Within Monday - Friday
    By timharding in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2013, 08:17 PM
  2. How to count all dates in column A using last friday and and next friday friday
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2011, 04:33 PM
  3. Use Ontime Only Monday through Friday
    By prescient in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2008, 07:35 PM
  4. Monday to Friday
    By lunar_star in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-09-2007, 12:22 AM
  5. [SOLVED] Help change Friday to following Monday
    By David in forum Excel General
    Replies: 4
    Last Post: 06-21-2006, 08:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1