Excel: If and sumproduct ? formula

**jodywn** · 03-31-2009, 04:07 AM

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

**DonkeyOte** · 03-31-2009, 04:22 AM

You could use a Sumproduct, eg:

B2: =SUMPRODUCT(--(Worksheet2!$A$2:$A$1000-DAY(Worksheet2!$A$2:$A$1000)+1=$A2),Worksheet2!$E$2:$E$1000)

Or you could use 2 SUMIF functions

B2: =SUMIF(Worksheet2!$A$2:$A$1000,">="&$A2,Worksheet2!$E$2:$E$1000)-SUMIF(Worksheet2!$A$2:$A$1000,">="&DATE(YEAR($A2),MONTH($A2)+1,1),Worksheet2!$E$2:$E$1000)

Or better yet consider running a Pivot Table off Worksheet2 and Group the Date by Month & Year thereby dispensing with need for formulae altogether.

Note: you may need to adapt the above to reflect your own Regional Settings (ie ; instead of , etc...)

**franciz** · 03-31-2009, 05:19 AM

try this in B2 of Sheet1

=SUMPRODUCT((MONTH(Sheet2!A2:A10)=1)*(Sheet2!E2:E10))

This will give you the sum in col E for Jan. You need to adjust/change the Sheet name
and rows to suit yours

HTH

cheers, francis

**DonkeyOte** · 03-31-2009, 05:21 AM

hi francis, you should add a YEAR clause also else you are combining Jan 2009 with Jan 2010...

the method I highlighted in my prior post is just one way of handling dates (month & year) within one test (forces all dates to be the 1st of the Month prior to evaluation and does not require coercion of data types)...

as you've ably demonstrated there are plenty of others... another method requiring just 1 test but which requires data type coercion would be:

B2: =SUMPRODUCT(--(TEXT(Worksheet2!$A$2:$A$1000,"MMYY")=TEXT($A2,"MMYY")),Worksheet2!$E$2:$E$1000)

xld/Bob has argued that text coercion is slower than conducting MONTH & YEAR tests but I 'believe' the date-day+1 approach to be quicker still...

**franciz** · 03-31-2009, 05:50 AM

Hi Luke, Thanks for pointing out.
You're right, I didn't read the last sentence.
Your formulas are of creative and innovative which are a joy to learn.

What I have provided is of traditional way so that starters can learn and move up gradually

Jody, my original formula change to this after taking into account in the different years
=SUMPRODUCT((MONTH(Sheet2!A2:A10)=1)*(YEAR(Sheet2!A2:A10)=2009)*(Sheet2!E2:E10))

This will add up values in column E for all data fall under Jan and 2009

HTH

cheers, francis