Sum nested within Sumproduct

I have a spreadsheet that lists various positions by account at original term to maturity (ex below). For this account the WAM (using SUMPRODUCT) is 128 days.

Account Amount Term
133400101 $75,000,000 182
133400101 $100,000,000 182
133400101 $100,000,000 92
133400101 $150,000,000 91

I would like to extract a WAM for each account. My problem is that the spreadsheet contains over 50 accounts and each account has at least 15 entries (the accounts are in sequence). Thus I need to write a sumproduct that looks only at a specific account. I have tried to write a sumproduct in which IFSUM's are the 1st and 2nd arrays. This does not work. Any ideas?

Originally Posted by MCII

I have a spreadsheet that lists various positions by account at original term to maturity (ex below). For this account the WAM (using SUMPRODUCT) is 128 days.

Account Amount Term
133400101 $75,000,000 182
133400101 $100,000,000 182
133400101 $100,000,000 92
133400101 $150,000,000 91

I would like to extract a WAM for each account. My problem is that the spreadsheet contains over 50 accounts and each account has at least 15 entries (the accounts are in sequence). Thus I need to write a sumproduct that looks only at a specific account. I have tried to write a sumproduct in which IFSUM's are the 1st and 2nd arrays. This does not work. Any ideas?

You should be able to do a SUMIF. What is WAM exactly... Weighted average something or other?

Anyway, assuming your accounts information is as above in columns A:C starting in row 2, you could do something as follows:

Put an account number in column E, and beside each account put the following formula: (Assumes 1000 rows, you can change it though)

Please Login or Register  to view this content.

or if you don't like errors, you can put:

Please Login or Register  to view this content.

I'm assuming you're just straight dividing, although you could do things differently quite easily.

S

WAM stands for weighted average maturity. Sumproduct will provide for this. In my example the calculated WAM is 129 days. Without doing any calcs consider the 'term' column. The range is between 91 and 182 days. Thus the WAM would fall somewhere within that range. SUMIF will not take into account the weight of each line item. Using strickly SUMIF returns an answer of 547 days. The problem when using the SUMPRODUCT is that cannot it cannot 'parse' out and aggregate specific accounts when there is more than one account. Thoughts??

My issue has been solved with the following:

=SUMPRODUCT(--(Sheet2!$R$2:$R$3500=$C10),Sheet2!$S$2:$S$3500,Sheet2!$AF$2:$AF$3500)/SUMIF(Sheet2!$R$2:$S$3500,C10,Sheet2!$S$2:$S$3500)

Note that 'C10' refers to the account that is to be looked up. Column S and R in Sheet2 refers the balance and account # respectively. Column AF refers to the original term (in days) of the deposit/loan. This function allows the sumproduct to function when looking up a table that has multiple accounts, and provides the weighted average of that specific lookup ignoring all other accounts. Not sure why the -- is required, but the formula does not work without them.