Search code examples
excelexcel-formulaexcel-2010worksheet-function

Allocate total to "accounts" based on percentage

Using Excel 2010. I have a list of people in column A, and a list of associated totals in column B. Each person's total is going to be divided between X amount of accounts.

Example: Bill owes $1000.00. Accounts 1, 2 and 3 are going to pay an equal share of his total.

I then have a second sheet listing each account, and I would like calculate the total each account is going to be paying.

Example: Account #4 is paying 50% of Mary's total, 33% of Eric's total and 75% of Andy's total.

Example of details sheet:

Name    Total      Acc1      Acc2         Acc3        Acc4

Bill    1000.00      33%      33%          33%
Mary   2000.00                50%                     50%

Example of summary sheet:

Account             Total

Acc1                333.33
Acc2                1333.33
Acc3                333.33
Acc4                1000.00

I know I could hand enter the formulas for the account totals, but I have a lot of accounts, and doing it all manually would take hours.

Any way to get this result with a copiable formula?

Solution

  • Assuming a layout as below, try =SUMPRODUCT($B2:$B4,C2:C4) (copied across)

    SO18000179 example

    The headings (C1:F1) can be copied and transposed to A7:A10 but the values would need to be dragged to B7:B10 individually - from where the array (A7:B10) can be copied and Paste Special Values into a different sheet.