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

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)

--
Jacob


"Vaughan" wrote:

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

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)

--
Biff
Microsoft Excel MVP


"Vaughan" wrote in message
...
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