Sumproduct Formula - With date range and multiple criteria

Hi there - I'm new to the forum but have been impressed with the responses I've seen to date.

Here is my required result: SUM OF FIELD 3, WHEN [FIELD 1 or FIELD 2 = ORK] AND [FIELD 4 = LOCATION 1] AND [FIELD 5 BETWEEN 10/1/2012 AND 10/31/12]

FIELD 1 FIELD 2 FIELD 3 FIELD 4 FIELD 5
ORK $10.00 Location 1 10/1/2012
ORK ORK $10.00 Location 1 10/15/2012
ORK DM14 $10.00 Location 2 10/10/2012
DM13 $400.00 Location 1 10/5/2012



Issue: When ORK appears in BOTH field 1 and 2, the sum result is duplicated

Formula so far: =SUMPRODUCT((DUMP!$E$2:$E$300000="Location 1")*((LEFT(DUMP!$B$2:$B$300000,3)="ORK")+(LEFT(DUMP!$C$2:$C$300000,3)="ORK"))*((DUMP!$F$2:$F$300000>=DATE(2012,10,1))*((DUMP!$F$2:$F$300000<=DATE(2012,10,31)))*(DUMP!$D$2:$D$300000)))
RESULT = $30 (RESULT SHOULD BE $20)

Formula:

Please Login or Register  to view this content.

Use cells to hold the criteria:

A2 = Location 1
B2 = ORK
C2 = 10/1/2012

Then:

=SUMPRODUCT(--(Dump!D2:D5=A2),SIGN((LEFT(Dump!A2:A5,3)=B2)+(LEFT(Dump!B2:B5,3)=B2)),--(Dump!E2:E5>=C2),--(Dump!E2:E5<=EOMONTH(C2,0)),Dump!C2:C5)

You're welcome. Thanks for the feedback!