avgplusguy
asked on
Excel 2007 Incomplete Date Sum Array lookup
MS Excel 2007 SVC Pack1 XP Service Pack3
Tab Input Cell B4 = 03/18/2012
Tab Visit Type1 row 2 starting dates row 3 - 9 Monday - Sunday Row 10 Weekly Totals
Yearly averages can be approximate and are computed with an array
{=SUM((IF(YEAR(Input!$B$4) =YEAR($B$2 :$HI$2),$B 3:$HI9,FAL SE)))}
However Current monthly totals must be exact
Reduced screenshot
Week of Week of Week of
27-Feb 5-Mar 12-Mar
Monday 60 44 62
Tuesday 58 52 53
Wednesday 53 59 56
Thursday 44 45 46
Friday 63 45 64
Saturday 48 45 51
Sunday 39 42 57
Wkly Total 365 332 389
02/27 = 60, 02/28=58, 02/29=53, 03/01 = 44, 03/02=63, 03/03=48, 03/04=39 03/18=57
I need to add March and only March starting with 03/01 of 44 and ending with 03/18 57 for a total of 915
visit2.xls
Tab Input Cell B4 = 03/18/2012
Tab Visit Type1 row 2 starting dates row 3 - 9 Monday - Sunday Row 10 Weekly Totals
Yearly averages can be approximate and are computed with an array
{=SUM((IF(YEAR(Input!$B$4)
However Current monthly totals must be exact
Reduced screenshot
Week of Week of Week of
27-Feb 5-Mar 12-Mar
Monday 60 44 62
Tuesday 58 52 53
Wednesday 53 59 56
Thursday 44 45 46
Friday 63 45 64
Saturday 48 45 51
Sunday 39 42 57
Wkly Total 365 332 389
02/27 = 60, 02/28=58, 02/29=53, 03/01 = 44, 03/02=63, 03/03=48, 03/04=39 03/18=57
I need to add March and only March starting with 03/01 of 44 and ending with 03/18 57 for a total of 915
visit2.xls
ASKER
Dave,
That will probably give me the approximation based on the starting week.
March total is 332 + 389 + 44 +63 + 48 + 39. Two weeks are totally in March and there are four March Days in the week that started in February. The bad part is the date only applies to row 3. I am thinking this might work better in Access than Excel. Normally you have to worry about the first and last week of the month.
That will probably give me the approximation based on the starting week.
March total is 332 + 389 + 44 +63 + 48 + 39. Two weeks are totally in March and there are four March Days in the week that started in February. The bad part is the date only applies to row 3. I am thinking this might work better in Access than Excel. Normally you have to worry about the first and last week of the month.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great work guys. Both solutions worked for March and February. Barry's solution works for January which has one day listed under Dec 26th that Dave does not capture.
Dave got the ball rolling and I almost understood his solution. how does -row($A$3) almost make this work at the end?
Barry I copied and pasted your solution. The braces are typed as braces or do they have to be entered in with the Cntrl+Shift+Enter?
Dave got the ball rolling and I almost understood his solution. how does -row($A$3) almost make this work at the end?
Barry I copied and pasted your solution. The braces are typed as braces or do they have to be entered in with the Cntrl+Shift+Enter?
Mine didn't work because we were using Year comparison in the equation and needed to treat the weekdays the same as the month.
The corrected formula is:
=SUMPRODUCT(($B$3:$M$9)*(Y EAR($B$2:$ M$2+ROW($A $3:$A$9)-R OW($A$3))= YEAR(Input !B4))*(MON TH($B$2:$M $2+ROW($A$ 3:$A$9)-RO W($A$3))=M ONTH(Input !B4)))
It sounds like you have an understanding of barry's solution, so I guess you're good.
Mine checks the year of the date and the month of the date and compares to the input. the +Row($A$3:$A$9)-row($A$3) is similar math to add the 0-6 days from the top date as barry's equation does.
Cheers,
Dave
The corrected formula is:
=SUMPRODUCT(($B$3:$M$9)*(Y
It sounds like you have an understanding of barry's solution, so I guess you're good.
Mine checks the year of the date and the month of the date and compares to the input. the +Row($A$3:$A$9)-row($A$3) is similar math to add the 0-6 days from the top date as barry's equation does.
Cheers,
Dave
Use this revised formula:
=SUMPRODUCT(($B$3:$HI$9)*(
Also, your original array formula could be written as:
=SUM((IF((MONTH(Input!$B$4
CTRL+SHIFT+ENTER to confirm
Dave