+ Reply to Thread
Results 1 to 3 of 3

Autosum using WEEKDAY and WEEKNUM

  1. #1
    Registered User
    Join Date
    07-10-2007
    Posts
    8

    Autosum using WEEKDAY and WEEKNUM

    My Excel programming level is just above beginner and I tend to think in terms of BASIC programming. Sorry!
    I've attached my Excel 2007 file.
    I am racking my brain trying to figure things out. I can set formulas manually but it seems to me that a function is the better way as it can be used year to year.

    In cells A2-A29 up to A32 (changes due to deferring days in a month) I have the date set.
    Adjacent cells (col B) have my daily sales.

    The 2 things I'm trying to accomplish are:
    1) Cell E3 should contain the sum of week 1, E4 Week 2 etc. I assume that WEEKNUM can do this but I need it whittled down to actual calender days for that week. If the first or last week only have 2 or 3 days, It should recognize that and only add those days.
    2) Cell I3 should contain the sum of all Sundays. I have accomplished this by using =SUM(B4,B11,B18,B25) but that is only going to work for this year. The WEEKDAY function should handle this fine. I just can't figure out how to do a formula combining it into an IF statement and total all Sunday's in the month.
    Walking my thought process through, here is where I'm at:
    IF (A2,1) =1 then add B2 to the total, if not skip it and continue to A3 etc. Maybe set a variable and increase it by B2?

    My goal is to be able to use this year to year, only changing the date in A2 to the current year and have it automatically update. I will be making a separate tab for each month but all in the same workbook.

    Any help is appreciated.
    Attached Files Attached Files
    Last edited by emillerd; 01-17-2013 at 06:33 PM.

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Autosum using WEEKDAY and WEEKNUM

    There is probably a way of doing this without the helper columns, but you can easily hide them.
    I also think that by using helper columns, the formulas are pretty basic, and easy for you to change and manipulate if needed.
    Attached Files Attached Files
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    07-10-2007
    Posts
    8

    Smile Re: Autosum using WEEKDAY and WEEKNUM

    That's exactly what I needed! Thank you and I agree that keeping the formulas simple makes it easier for me to change them to fit different months. I can just hide the columns I do not want to see/print.
    Again, Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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