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 -------- BDATE --- BUDGETED HOURS01/09 ---- “formula”02/0903/0901/1002/10Worksheet2 (week):A ------- EDATE --- BUDGETED HOURS 01/03/09 --- 2201/07/09 --- 1901/28/09 --- 302/06/09 --- 503/05/09 --- 1103/22/09 --- 2301/07/10 --- 1301/19/10 --- 002/08/10 --- 3I 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 thanksJody

See More: Excel: if and sumproduct? formula

#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