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/10Worksheet2 (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 --- 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 thanks

Jody

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))

BTW...I don't use them too often, but I believe that Pivot Tables are perfectly suited for this type of calculation.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History