Try this:

=SUMPRODUCT(--(A2:A9=F2),--(LEFT(B2:B9,6)=G2),--(INT(D2:D9)=H2),--(INT(D2:D9)<=I2),C2:C9)

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Formatting will make no difference. The real question is are they really
dates? Select one of the date and choose Format, Cells, Number tab - is
the
Date category selected? If not they are not dates.

Let us know what you find.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"jonny" wrote:

On 28 Jan, 22:05, Shane Devenshire
wrote:
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

Shane,

Quick question..

I've just realised that my dates are formatted as "01/12/2008 15:44"
and this seems to be stopping the formula from working, is there a way
around this?

By the way I can't see a "yes" button on your posts, but you are
helping!

Thanks,

Jon