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.
Bookmarks