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

#### JesseCB

##### New Member
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.

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

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

#### KRice

##### Well-known Member
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
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
Great news! You're welcome...I'm happy to help. And welcome to the MrExcel board.

Replies
5
Views
53
Replies
9
Views
86
Replies
13
Views
235
Replies
3
Views
68
Replies
2
Views
285