software

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(quot;BU1quot;,OFFSET(BUs,, ,1),0)-1,ROWS(months)-1,1)
)*(OFFSET(months,1,MATCH(quot;Janquot;,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)

gt; wrote in message oups.com...
gt; I posted this wrong the first time, sorry. Here is the corrected
gt; problem. I have the following 3 tables and am trying to create an
gt; array formula for the 4th:
gt;
gt; Name, Total Amount
gt; Job 1, 24000
gt; Job 2, 66000
gt; Job 3, 35000
gt;
gt; Name, Jan, Feb, Mar, Apr, etc
gt; Job 1, 0%, 50%, 20%, 0%, etc
gt; Job 2, 10%, 0%, 20%, 50%, etc
gt; Job 3, 15%, 85%, 0%, 0%, etc
gt;
gt; Name, BU1, BU2, BU3, BU4
gt; Job 1, 20%, 50%, 30%, 0%
gt; Job 2, 0%, 40%, 60%, 0%
gt; Job 3, 10%, 0%, 0%, 90%
gt;
gt; What I'm looking for:
gt;
gt; All Jobs, Jan, Feb, Mar, etc
gt; BU1, sumproducts (Jan would be 24,000 * 0% * 20% 66,000 * 10% * 0%
gt; 35,000 * 15% * 10%)
gt; BU2, sumproducts (Jan would be 24,000 * 0% * 50% 66,000 * 10% * 40%
gt; 35,000 * 15% * 0%)
gt; BU3, sumproducts
gt;
gt; I would like to use array formula instead of PivotTables if possible.
gt; Thanks for all your help!
gt;
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)

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