Hi,

You can also use the slightly shorter

=SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=--"1/1/09"),--(D1:D8<=--"7/1/09"),C1:C8)

Or you can reference two cells with the dates (A1 & A2)

=SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=A1),--(D1:D8<=A2),C1:C8)
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"jonny" wrote:

Hi All,

I have a table something like the below:
Column A, B, C, D
bob, ght991xyz, 10, 10/01/09
jan, ght887fht, 100, 03/01/09
Bob, ght991xyz, 50, 09/01/09
jan, ght887fht, 7, 12/02/09
bob, ght991xyz, 10, 04/02/09
jan, ght887fht, 200, 02/01/09
rupert, ght991xyz, 50, 07/01/09
jan, ght887fht, 7, 09/01/09

I'm currently using the following formula:
=SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9))
the above checks the first 6 characters of Column B are equal to
"GHT991" and then sums the value in column c for the rows where this
is the case...

What I now need to do is find a way to sum column C only when Column A
is equal to "bob" and column B begins with "GHT991" and where column D
is between a specified date range say 01/01/09 and 07/01/09 (I'm UK
based by the way so it's ddmmyy).

Any help much appreciated.. I'm stumped!

Thanks,

Jon