MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Array formula - Aladin?


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.

Thanks in advance,

Russell


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

How about
=SUMPRODUCT((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1)*(Sheet1!D1:D10))
Adjust the A1:a10 etc to your actual data set.

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)

=IF((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),(Sheet1!D1:D10),"NOT FOUND")

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.

Aladin

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

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.

Aladin

Posted by Russell Hauf on November 20, 2001 11:17 AM

Thanks to you both (nm)