Hello,

I have been trying to build this formula for sometime but just can't
get one part right, and I know if I ask for help I will get it. So, my
spreadsheets look as follows:

Sheet1

G | H | L

1111000000 | 05-12-05 |
2222000000 | 06-12-05 |
7777000000 | 08-12-05 |
1111000000 | 05-12-05 |
4444000000 | 16-12-05 |
2222000000 | 06-12-05 |
2222000000 | 31-12-05 |
1111000000 | 31-12-05 |




Sheet2

C | D | G

1111000000 | 05-12-05 | 200
2222000000 | 06-12-05 | 46
7777000000 | 08-12-05 | 37
1111000000 | 05-12-05 | 11
4444000000 | 16-12-05 | 131
2222000000 | 06-12-05 | 120
2222000000 | 31-12-05 | 54
1111000000 | 31-12-05 | 78

I want the total of Column G of Sheet2 entered in Column L of Sheet1
but entered only for the first listing when there are multiple listings
having similar data in both Columns C and D, e.g., the first and fourth
rows of Sheet2 are similar to C6 and D6. Similarly, I want the total
of Columns G of Sheet2 entered in Column Q of Sheet1 but entered only
for the first listing when there are multiple listings.

I have:

=SUMPRODUCT(--(Sheet2!$D$3:$D$20=$H6),--(Sheet2!$C$3:$C$20=$G6),Sheet2!$G$3:$G$20)

in the first row of Column of Sheet1;

=IF(AND(OR(ISNA(VLOOKUP(G7,$G$6:G6,1,FALSE)=TRUE), ISNA(VLOOKUP(H7,$H$6:H6,1,FALSE)=TRUE)),SUMPRODUCT (--(G7:H7=$G$6:H6))=0),SUMPRODUCT(--(Sheet2!$D$3:$D$20=$H7),--(Sheet2!$C$3:$C$20=$G7),Sheet2!$G$3:$G$20),0)

in the next row (which I copied down).

The result is:

211
166
37
-
131
-
54
-

The last row should be 78.

Can anyone help fix the formula?

Thanks,
Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=512327