Sumif or sumproduct with multiple & difficult criteria

[Vaughan]

Im trying to sum a column based on other columns containing certain criteria.
For instance the example below im after the sum of idle delays on the 1st.
But it gets more complex when i want the sum of any delay containing "idle",
in the week 1-7/1/09

Eg.

Date Delay Time(h)
1/1/09 Idle time 0.3
1/1/09 Idle / external 0.4
1/1/09 Breakdown 1
2/1/09 Breakdown 2
2/1/09 Idle / mech 3
2/1/09 Idle / external 4

 

[Jacob Skaria]

With your data in ColA,ColB and ColC and the query start date in cell E1 try
the below formula; which will sum up values in C1:C100 if ColB contains
'Idle' and the date range mentionedin cell E1+7

E1= 1/1/09

=SUMPRODUCT(--(ISNUMBER(SEARCH("idle",B1:B100)))*
(A1:A100>=E1)*(A1:A100<=E1+6),C1:C100)

 

[T. Valko]

One way...

Use cells to hold the date boundaries:

E2 = start date
F2 = end date

=SUMPRODUCT(--(A2:A7>=E2),--(A2:A7<=F2),--(ISNUMBER(SEARCH("idle",B2:B7))),C2:C7)