Using Sumproduct - Excel 2003

[BJ&theBear]

I am currently working on a spreadsheet where I need to solve two
problems both of which I think can be done with Sumproduct but
unfortunately I am having difficulty getting my head round it.

I basically want to add up the corresponding entries which satisfy two
conditions and then three conditions

The worksheet called "chargeablehours" has multiple columns but the
four important ones are:-

Column A - Project ID

Column D - CLSStage

Column H = Date

Column I - Hoursworked

In an seperate worksheet "Menu" (within the same workbook) I want to
be able to do two seperate calculations using the information input
into

A1 - input Project ID

B1 - input CLSstage

C1 - input startdate

D1 - input enddate

The first formula needs to extract all entries where the ProjectID and
CLSstage are equal to A1 and B1 and sum the hoursworked in Column I

The second formula needs to extract exactly the same information (sum
hoursworked) but where the dates in column H are between the input
startdate and enddate.

I hope this makes sense

Any help would be most appreciated

BJthebear

 

[Bernard Liengme]

First formula:
=SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1200=Menu!A2),ChangeableHours!I1:I200)
adjust ranges as needed; you cannot use full column references as in A:A
The reference to sheet name Menu is not required
=SUMPRODUCT(--(ChangeableHours!A1:A200=A1),--(ChangeableHours!D1200=A2),ChangeableHours!I1:I200)

Could also use named ranges

Second Q is ambiguous
either
=SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1020=Menu!A2),--(ChangeableHours!H1:H200>=Menu!A3),--(ChangeableHours!H1:H20=Menu!A4),ChangeableHours!I1:I200)

or

=SUMPRODUCT(--(ChangeableHours!H1:H200>=Menu!A3),--(ChangeableHours!H1:H200=Menu!A4),ChangeableHours!I1:I200)

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct
best wishes

 

[BJ&theBear]

Thanks for the help

I fully understand the first part but cannot fathom out how the second
part actually calculates whether the date in Column H is between the
startdate and enddate. To my "simple mind" all it is doing is
checking for entries with the same start date returning true or false
and then doing the same with the end date and as there is only one
date in column H it will never be able to come up with 2 x true and it
will always return false as there is only one date - this will mean
that it cannot find any matches??!!

Am I missing something here?

Thanks once again


BJthebear

 

[Bernard Liengme]

Sorry, I misread your question: I had this info
A1 - input Project ID
B1 - input CLSstage
C1 - input startdate
D1 - input enddate
as
A1 - input Project ID
A2 - input CLSstage
A3 - input startdate
A4 - input enddate
So we need to change
=SUMPRODUCT(--(ChangeableHours!H1:H200>=A3),--(ChangeableHours!H1:H200=A4),ChangeableHours!I1:I200)

to

=SUMPRODUCT(--(ChangeableHours!H1:H200>=C1),--(ChangeableHours!H1:H200<=D1),ChangeableHours!I1:I200)

I had also omitted a "<" in the second argument when I copied this formula
from my test worksheet.
So what this does is:

First argument) if the date in H is equal to or greater than C1 we get a 1,
else we get a zero. Suppose for the first five records we get {0,0,1,1,1} -
the last three on or after C1's date

Second argument) if the date in H is equal to or less than D1 we get a 1,
else we get a zero. Suppose for the first five records we get {1,1,1,1,0} -
the first 4 are on of before D1's date

When these get multiplied
{0,0,1,1,1}* {1,1,1,1,0} ={0,0,1,1,0} -- records 3 and 4 lie between the
two dates (inclusively)

Third argument) returns hours worked: so {20,30,20,40}
Multiple this by the result of first two: ={0,0,1,1,0}*{20,30,20,40} =
{0,0,30,20,0}
We have run out of arguments, so SUMPRODUCT adds the array
{0 +0 +30 +20 + 0} = 50
This sums the hours for the record that lie between two dates
best wishes