First formula:
=SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D200=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!D1:D200=A2),ChangeableHours!I1:I2 00)
Could also use named ranges
Second Q is ambiguous
either
=SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D020=Menu!A2),--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H20=Menu!A4),ChangeableHours!I 1: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/xlFunctio...tml#SumProduct
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"BJ&theBear" wrote in message
...
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 a-
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