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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
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
1,141
Office Version
  1. 365
Platform
  1. Windows
Great news! You're welcome...I'm happy to help. And welcome to the MrExcel board.
 

Forum statistics

Threads
1,148,269
Messages
5,745,782
Members
423,972
Latest member
franklins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top