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

Use this formula for row 2 down

=IF(ISNUMBER(MATCH(G7&H7,$G$6:G6&$H$6:H6,0)),"",SU M(IF((Sheet2!$D$3:$D$20=$H
7)*(Sheet2!$C$3:$C$20=$G7),Sheet2!$G$3:$G$20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gos-C" wrote in
message ...

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

Yes, it works!

Thanks, Bob

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

Hi,

I have just discovered a problem - re Bob Phillips post "Use this
formula for row 2 down."

For some unknown reason, it does not return a match when row 2 matches
row 1.

Any help?

Thank you,
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