Hi all,
I am new to this forum and so apologise if I have posted this to the incorrect thread, etc.
What I am trying to do is quite simple in theory, but I cannot get a variation of SUMIFS, SUM and IF to do the job.
The context is this: I am looking to create a resource for a client couple. They receive income from a number of different streams - some joint investments, some singular. I want to list each of the incomes, where they've come from, when they were paid, and automatically add up how much income each client made within any given period.
Column Headings:
A = Payment received date
B = Payment source ("rental income", "Client 1 PAYG salary", "dividends", etc.)
C = Client 1's % ownership of the income (from 0%-100%)
D = Client 2's % ownership of the income
E = Total payment amount
What I need is to auto-calculate which payments were received (column A) between a given date range (which would be specified in, say, $F$1 and $F$2). This would be easily achievable with a SUMIFS formula. However, this would give me the total amount received within this date range. What I then need, is for it to multiply each payment amount (column F) with each client's respective % ownership (columns C and D).
For example:
Column A | Column B | Column C | Column D | Column E
Date | Source | Client 1 % ownership | Client 2 % ownership | Total Payment amount
01/01/2020 | Client 1 PAYG Salary | 100% | 0% | $2000
08/01/2020 | Client 2 PAYG Salary | 0% | 100% | $1500
15/01/2020 | Joint Rental Income | 50% | 50% | $500
15/01/2020 | Client 1 PAYG Salary | 100% | 0% | $2000
22/01/2020 | Client 2 PAYG Salary | 0% | 100% | $1500
23/01/2020 | Client 2 Dividend Payment | 0% | 100% | $800
With 05/01/2020 as the period start date in $F$1 and 22/01/2020 as the period end date in $F$2, I would expect the formula to return $2250 ($1500*0% + $500*50% + $2000*100% + $1500*0%) for Client 1 and $3250 ($1500*100% + $500*50% + $2000*0% + $1500*100%) for Client 2.
The SUMIFS formula is =SUM(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]
I could make criteria_range1 and criteria_range2 $A:$A
I could also make criteria1 ">"&$F$1 and criteria2 "<"&$F$2
But it's almost like I need the sum_range to be PRODUCT($E:$E, $C:$C) for Client 1's cell, and PRODUCT($E:$E, $D:$D) for Client 2's cell. But this does not appear to be possible.
Please don't recommend any changes to the layout of the sheet as it needs to stay in this form. I would very much appreciate some assistance in receiving this result.
Thank you all,
Jesse.
I am new to this forum and so apologise if I have posted this to the incorrect thread, etc.
What I am trying to do is quite simple in theory, but I cannot get a variation of SUMIFS, SUM and IF to do the job.
The context is this: I am looking to create a resource for a client couple. They receive income from a number of different streams - some joint investments, some singular. I want to list each of the incomes, where they've come from, when they were paid, and automatically add up how much income each client made within any given period.
Column Headings:
A = Payment received date
B = Payment source ("rental income", "Client 1 PAYG salary", "dividends", etc.)
C = Client 1's % ownership of the income (from 0%-100%)
D = Client 2's % ownership of the income
E = Total payment amount
What I need is to auto-calculate which payments were received (column A) between a given date range (which would be specified in, say, $F$1 and $F$2). This would be easily achievable with a SUMIFS formula. However, this would give me the total amount received within this date range. What I then need, is for it to multiply each payment amount (column F) with each client's respective % ownership (columns C and D).
For example:
Column A | Column B | Column C | Column D | Column E
Date | Source | Client 1 % ownership | Client 2 % ownership | Total Payment amount
01/01/2020 | Client 1 PAYG Salary | 100% | 0% | $2000
08/01/2020 | Client 2 PAYG Salary | 0% | 100% | $1500
15/01/2020 | Joint Rental Income | 50% | 50% | $500
15/01/2020 | Client 1 PAYG Salary | 100% | 0% | $2000
22/01/2020 | Client 2 PAYG Salary | 0% | 100% | $1500
23/01/2020 | Client 2 Dividend Payment | 0% | 100% | $800
With 05/01/2020 as the period start date in $F$1 and 22/01/2020 as the period end date in $F$2, I would expect the formula to return $2250 ($1500*0% + $500*50% + $2000*100% + $1500*0%) for Client 1 and $3250 ($1500*100% + $500*50% + $2000*0% + $1500*100%) for Client 2.
The SUMIFS formula is =SUM(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]
I could make criteria_range1 and criteria_range2 $A:$A
I could also make criteria1 ">"&$F$1 and criteria2 "<"&$F$2
But it's almost like I need the sum_range to be PRODUCT($E:$E, $C:$C) for Client 1's cell, and PRODUCT($E:$E, $D:$D) for Client 2's cell. But this does not appear to be possible.
Please don't recommend any changes to the layout of the sheet as it needs to stay in this form. I would very much appreciate some assistance in receiving this result.
Thank you all,
Jesse.
