+ Reply to Thread
Results 1 to 14 of 14

want to count number of occurances on weekdays/weekends

  1. 01-13-2014, 07:15 PM #1
    phango13
    phango13 is offline
    Registered User
    Join Date
    01-13-2014
    Location
    sf.ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    want to count number of occurances on weekdays/weekends

    I'm trying to help a friend simplify her invoicing for dogwalking services--I set up a monthly tracking page that has the actual date in the first row, and each dogs name in the first column...she and her employees can enter either a "1" or a "2" for each dog on each day (1 means the dog got a single walk, 2 for double walk)...I then want to create a second sheet that sums for each dog the number of weekday single walks, weekend single walks, weekday double walks or weekend double walks for the month (all four have different unit costs)

    I've been trying nest the WEEKDAY functions to look at the date in row 1 to determine if its a weekend (WEEKDAY=1 or WEEKDAY=7) and then use COUNTIF to sum the number of 1's and 2's but can'dog_tracking_v2.xlsxt figure it out--any help truly appreciated!! thx...
    Reply With QuoteRegister To Reply
  2. 01-13-2014, 07:24 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: want to count number of occurances on weekdays/weekends

    Hi,

    It's probably best if you upload the workbook with some sample entries and manually add the results you want to see, clearly identifying which cells are data and which cells are results.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 01-13-2014, 07:33 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: want to count number of occurances on weekdays/weekends

    Hi and welcome to the forum

    Use this as the basis for the other 3, this will count by name, 1 walk for weekdays...
    =SUMPRODUCT((data_entry!$A$2:$A$28=$B4)*(data_entry!$A$2:$AF$28=1)*(data_entry!$A$2:$AF$2<6))

    edit: @ Richard, the WB is in the last line of text, towards the end
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 01-13-2014, 07:41 PM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: want to count number of occurances on weekdays/weekends

    @FD

    Thanks for that. I Completely missed it. In mitigation it was quite well hidden
    Reply With QuoteRegister To Reply
  5. 01-13-2014, 07:47 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: want to count number of occurances on weekdays/weekends

    C4: =SUMPRODUCT(--(data_entry!$B3:$AF3=1))-E4
    D4: =SUMPRODUCT(--(data_entry!$B3:$AF3=2))-F4
    E4: =SUMPRODUCT(--(data_entry!$B3:$AF3=1),--(WEEKDAY(data_entry!$B$2:$AF$2)=1)+--(WEEKDAY(data_entry!$B$2:$AF$2)=7))
    F4: =SUMPRODUCT(--(data_entry!$B3:$AF3=2),--(WEEKDAY(data_entry!$B$2:$AF$2)=1)+--(WEEKDAY(data_entry!$B$2:$AF$2)=7))

    Now copy those four cells down.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  6. 01-13-2014, 07:51 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: want to count number of occurances on weekdays/weekends

    eeek I forgot to add that I added a helper row on sheet1 at row 2 and used this, copied across...
    =WEEKDAY(B1,2)
    Reply With QuoteRegister To Reply
  7. 01-13-2014, 08:04 PM #7
    phango13
    phango13 is offline
    Registered User
    Join Date
    01-13-2014
    Location
    sf.ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: want to count number of occurances on weekdays/weekends

    ahh, thanks Ford!! I was about to respond that I was getting the same counts for weekdays and weekends...sorry about hiding the WB Richard, and Jerry--about to try your formulas, thanks all!!!!
    Reply With QuoteRegister To Reply
  8. 01-13-2014, 08:25 PM #8
    phango13
    phango13 is offline
    Registered User
    Join Date
    01-13-2014
    Location
    sf.ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: want to count number of occurances on weekdays/weekends

    ok Ford--worked with helper row, thanks!!!
    Reply With QuoteRegister To Reply
  9. 01-14-2014, 02:40 PM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: want to count number of occurances on weekdays/weekends

    Did you try mine, no helper needed?
    Reply With QuoteRegister To Reply
  10. 01-14-2014, 03:58 PM #10
    phango13
    phango13 is offline
    Registered User
    Join Date
    01-13-2014
    Location
    sf.ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: want to count number of occurances on weekdays/weekends

    i did, it's close...first dog counted 15/6/1/4 when I expected 14/9/2/1
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 01-14-2014, 05:53 PM #11
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: want to count number of occurances on weekdays/weekends

    When I open your file above, I see 14/9/2/1 for the first dog, you're expected results.
    Reply With QuoteRegister To Reply
  12. 01-14-2014, 06:06 PM #12
    phango13
    phango13 is offline
    Registered User
    Join Date
    01-13-2014
    Location
    sf.ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: want to count number of occurances on weekdays/weekends

    oops sorry Jerry--I posted the one with the helper row...here's what I get with yours
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 01-14-2014, 06:15 PM #13
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: want to count number of occurances on weekdays/weekends

    My apologies, I was considering Sunday as day 1. The formula is actually even simpler:

    C4: =SUMPRODUCT(--(data_entry!$B3:$AF3=1))-E4
    D4: =SUMPRODUCT(--(data_entry!$B3:$AF3=2))-F4
    E4: =SUMPRODUCT(--(data_entry!$B3:$AF3=1),--(WEEKDAY(data_entry!$B$2:$AF$2)>=6))
    F4: =SUMPRODUCT(--(data_entry!$B3:$AF3=2),--(WEEKDAY(data_entry!$B$2:$AF$2)>=6))
    Reply With QuoteRegister To Reply
  14. 01-14-2014, 08:07 PM #14
    phango13
    phango13 is offline
    Registered User
    Join Date
    01-13-2014
    Location
    sf.ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: want to count number of occurances on weekdays/weekends

    sweet, thanks! double solved...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Turning weekends into weekdays?
    By bobing in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-16-2013, 06:34 AM
  2. How do I count Weekdays only in a range of dates? Exclude Holidays, weekends.
    By c7scayman in forum Excel General
    Replies: 4
    Last Post: 03-08-2012, 02:57 PM
  3. [SOLVED] WEEKENDS VS. WEEKDAYS
    By TLAngelo in forum Excel General
    Replies: 0
    Last Post: 07-10-2006, 02:04 PM
  4. [SOLVED] WEEKENDS VS. WEEKDAYS
    By TLAngelo in forum Excel General
    Replies: 7
    Last Post: 05-12-2006, 12:35 PM
  5. [SOLVED] How do i count number of weekdays between two dates?
    By Sanjay Shah in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2005, 12:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1