software

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: www.excelforum.com/member.php...oamp;userid=14518
View this thread: www.excelforum.com/showthread...hreadid=512327Use this formula for row 2 down

=IF(ISNUMBER(MATCH(G7amp;H7,$G$6:G6amp;$H$6:H6,0)),quot;quot;,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)

quot;Gos-Cquot; gt; wrote in
message ...
gt;
gt; Hello,
gt;
gt; I have been trying to build this formula for sometime but just can't
gt; get one part right, and I know if I ask for help I will get it. So, my
gt; spreadsheets look as follows:
gt;
gt; Sheet1
gt;
gt; G | H | L
gt;
gt; 1111000000 | 05-12-05 |
gt; 2222000000 | 06-12-05 |
gt; 7777000000 | 08-12-05 |
gt; 1111000000 | 05-12-05 |
gt; 4444000000 | 16-12-05 |
gt; 2222000000 | 06-12-05 |
gt; 2222000000 | 31-12-05 |
gt; 1111000000 | 31-12-05 |
gt;
gt;
gt;
gt;
gt; Sheet2
gt;
gt; C | D | G
gt;
gt; 1111000000 | 05-12-05 | 200
gt; 2222000000 | 06-12-05 | 46
gt; 7777000000 | 08-12-05 | 37
gt; 1111000000 | 05-12-05 | 11
gt; 4444000000 | 16-12-05 | 131
gt; 2222000000 | 06-12-05 | 120
gt; 2222000000 | 31-12-05 | 54
gt; 1111000000 | 31-12-05 | 78
gt;
gt; I want the total of Column G of Sheet2 entered in Column L of Sheet1
gt; but entered only for the first listing when there are multiple listings
gt; having similar data in both Columns C and D, e.g., the first and fourth
gt; rows of Sheet2 are similar to C6 and D6. Similarly, I want the total
gt; of Columns G of Sheet2 entered in Column Q of Sheet1 but entered only
gt; for the first listing when there are multiple listings.
gt;
gt; I have:
gt;
gt;
=SUMPRODUCT(--(Sheet2!$D$3:$D$20=$H6),--(Sheet2!$C$3:$C$20=$G6),Sheet2!$G$3:
$G$20)
gt;
gt; in the first row of Column of Sheet1;
gt;
gt;
=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)
gt;
gt; in the next row (which I copied down).
gt;
gt; The result is:
gt;
gt; 211
gt; 166
gt; 37
gt; -
gt; 131
gt; -
gt; 54
gt; -
gt;
gt; The last row should be 78.
gt;
gt; Can anyone help fix the formula?
gt;
gt; Thanks,
gt; Gos-C
gt;
gt;
gt; --
gt; Gos-C
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Gos-C's Profile:
www.excelforum.com/member.php...oamp;userid=14518
gt; View this thread: www.excelforum.com/showthread...hreadid=512327
gt;

Yes, it works!

Thanks, Bob

Gos-C--
Gos-C------------------------------------------------------------------------
Gos-C's Profile: www.excelforum.com/member.php...oamp;userid=14518
View this thread: www.excelforum.com/showthread...hreadid=512327
Hi,

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

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: www.excelforum.com/member.php...oamp;userid=14518
View this thread: www.excelforum.com/showthread...hreadid=512327