# 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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### 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
288
Replies
0
Views
597
Replies
11
Views
403
Replies
4
Views
171
Replies
6
Views
180

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,784
Messages
5,833,682
Members
430,222
Latest member
Nickkarl

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

### Which adblocker are you using?

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

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