HELP - Complex SUMIF - SUM of PRODUCTS of two cells IF another cell is within a specified date range

JesseCB

New Member
Joined
May 9, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
I think a formula based on SUMPRODUCT may be better suited for this:
MrExcel20200509.xlsx
ABCDEFG
1Date Source Client 1 % ownership Client 2 % ownership Total Payment amount1/5/2020<--Begin
21/1/2020 Client 1 PAYG Salary 100%0%$2,000 1/22/2020<-- End
31/8/2020 Client 2 PAYG Salary 0%100%$1,500 Client 1Client 2
41/15/2020 Joint Rental Income 50%50%$500 22503250
51/15/2020 Client 1 PAYG Salary 100%0%$2,000
61/22/2020 Client 2 PAYG Salary 0%100%$1,500
71/23/2020 Client 2 Dividend Payment 0%100%$800
Sheet2
Cell Formulas
RangeFormula
F4F4=SUMPRODUCT($C$2:$C$7*$E$2:$E$7,--($A$2:$A$7>=$F$1),--($A$2:$A$7<=$F$2))
G4G4=SUMPRODUCT($D$2:$D$7*$E$2:$E$7,--($A$2:$A$7>=$F$1),--($A$2:$A$7<=$F$2))
 

JesseCB

New Member
Joined
May 9, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hi KRice,

Apologies for the late reply but this worked perfectly! Did exactly what I needed - thank you for your help!

Jesse.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
Great news! You're welcome...I'm happy to help. And welcome to the MrExcel board.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,787
Messages
5,542,509
Members
410,559
Latest member
jordansmith6532
Top