I posted this wrong the first time, sorry. Here is the corrected
problem. I have the following 3 tables and am trying to create an
array formula for the 4th:

Name, Total Amount
Job 1, 24000
Job 2, 66000
Job 3, 35000

Name, Jan, Feb, Mar, Apr, etc
Job 1, 0%, 50%, 20%, 0%, etc
Job 2, 10%, 0%, 20%, 50%, etc
Job 3, 15%, 85%, 0%, 0%, etc

Name, BU1, BU2, BU3, BU4
Job 1, 20%, 50%, 30%, 0%
Job 2, 0%, 40%, 60%, 0%
Job 3, 10%, 0%, 0%, 90%

What I'm looking for:

All Jobs, Jan, Feb, Mar, etc
BU1, sumproducts (Jan would be 24,000 * 0% * 20% + 66,000 * 10% * 0% +
35,000 * 15% * 10%)
BU2, sumproducts (Jan would be 24,000 * 0% * 50% + 66,000 * 10% * 40% +
35,000 * 15% * 0%)
BU3, sumproducts

I would like to use array formula instead of PivotTables if possible.
Thanks for all your help!

=SUMPRODUCT((OFFSET(BUs,1,MATCH("BU1",OFFSET(BUs,, ,1),0)-1,ROWS(months)-1,1)
)*(OFFSET(months,1,MATCH("Jan",OFFSET(months,,,1), 0)-1,ROWS(months)-1,1))*(O
FFSET(Jobs,1,1,ROWS(months)-1,1)))

etc.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

wrote in message
oups.com...
I posted this wrong the first time, sorry. Here is the corrected
problem. I have the following 3 tables and am trying to create an
array formula for the 4th:

Name, Total Amount
Job 1, 24000
Job 2, 66000
Job 3, 35000

Name, Jan, Feb, Mar, Apr, etc
Job 1, 0%, 50%, 20%, 0%, etc
Job 2, 10%, 0%, 20%, 50%, etc
Job 3, 15%, 85%, 0%, 0%, etc

Name, BU1, BU2, BU3, BU4
Job 1, 20%, 50%, 30%, 0%
Job 2, 0%, 40%, 60%, 0%
Job 3, 10%, 0%, 0%, 90%

What I'm looking for:

All Jobs, Jan, Feb, Mar, etc
BU1, sumproducts (Jan would be 24,000 * 0% * 20% + 66,000 * 10% * 0% +
35,000 * 15% * 10%)
BU2, sumproducts (Jan would be 24,000 * 0% * 50% + 66,000 * 10% * 40% +
35,000 * 15% * 0%)
BU3, sumproducts

I would like to use array formula instead of PivotTables if possible.
Thanks for all your help!

Wow! That's incredible. Seriously, give me your PayPal ID so I can
send you some money. Thanks so much.

Tempting ... but that was a response freely given with no expectation.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

wrote in message
ups.com...
Wow! That's incredible. Seriously, give me your PayPal ID so I can
send you some money. Thanks so much.