Posted by Russell Hauf on November 20, 2001 10:42 AM

...or anybody else that can help...

Ok, here's my problem, which should be easy for you. I tried to look up some past examples, but couldn't find what I wanted (at least not in a short period of time).

Sheet1:

ABC1, 1, 1, \$4;
ABC1, 1, 2, \$5;
ABC1, 1, 3, \$2;
ABC1, 2, 1, \$0;
ABC1, 2, 2, \$6;
ABC1, 2, 3, \$7;
...
ABC7, 22, 1, \$66;
ABC7, 22, 2, \$9;
ABC7, 22, 3, \$1;

(There are actually more columns on sheet1, which I will also want to pull from, but let's make this easy)

And on Sheet2:

ABC1, 2, 1;
ABC1, 2, 2;
ABC1, 2, 3;

--what I want to do is to bring in the 4th column from Sheet1 (dollar amounts) to Sheet2. I can't seem to do this with a simple "IF" array function (possibly because of the 3rd column numbers repeating?). Also, the numbers in the third column go higher than 3, and vary for each value in column A.

Russell

Posted by anybody else on November 20, 2001 10:50 AM

=SUMPRODUCT((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1)*(Sheet1!D1:D10))

good luck

Posted by Russell Hauf on November 20, 2001 10:57 AM

Re: Array formula...

That did it - thank you very much. I was using:

(array-entered)

This worked for the first row, but not for the rest. Can you tell me why?

Thanks again,

Russell

Posted by Aladin Akyurek on November 20, 2001 11:05 AM

Russell --

I interpret the question as one of multiconditional summing:

On Sheet2, enter:

=SUMPRODUCT((Sheet1!\$A\$1:\$A\$9=A1)*(Sheet1!\$B\$1:\$B\$9=B1)*(Sheet1!\$C\$1:\$C\$9=C1),Sheet1!D1:D9)

and copy down as far as needed.

=============

Posted by Aladin Akyurek on November 20, 2001 11:09 AM

Re: Array formula...

=SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1)*(Sheet1!D1:D10))

array-entered will work too.