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

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History