Excel: if and sumproduct? formula

March 31, 2009 at 00:53:11
Specs: Windows XP
Hello,
I need help with a formula:
I have two worksheets in an excel file:

Worksheet1 (month):
A -------- B
DATE --- BUDGETED HOURS
01/09 ---- “formula”
02/09
03/09
01/10
02/10

Worksheet2 (week):
A ------- E
DATE --- BUDGETED HOURS
01/03/09 --- 22
01/07/09 --- 19
01/28/09 --- 3
02/06/09 --- 5
03/05/09 --- 11
03/22/09 --- 23
01/07/10 --- 13
01/19/10 --- 0
02/08/10 --- 3

I want a formula for worksheet1, column B, that sums all the hours of Jan 09 (see worksheet2, column E), so I have the monthly hours (worksheet1) calculated from the weekly hours (worksheet 2).
I have to do the same for Feb 09, March 09, Jan 10 and Feb10, but I think if I have the formula for Jan09, I can modulate it myself.

I hope you can help me,
Many thanks
Jody


See More: Excel: if and sumproduct? formula

Report •


#1
March 31, 2009 at 06:25:01
Based on your example, try this array formula in Sheet1!B2, entered using Ctrl-Shift-Enter and dragged down to B6:

=SUMPRODUCT((MONTH(Sheet2!$A$2:$A$10)=MONTH(A2))*(YEAR(Sheet2!$A$2:$A$10)=YEAR(A2))*(Sheet2!$E$2:$E$10))


Report •

#2
March 31, 2009 at 06:27:16
BTW...I don't use them too often, but I believe that Pivot Tables are perfectly suited for this type of calculation.

Report •

Related Solutions


Ask Question