SUMPRODUCT with comparing condition

[navafolk]

Hi pros,
I have a table like this:

Date1	Date2	Amount
1	5	10
2	3	15
3	4	20

and somewhere else, my Date0 = 4

My calculation is total of all rows and each row is: (min ([Date0]+1,@[Date2]) - @[Date1]) x Amount. My expected result is 75, which is (4 x 10) + (1 x 15) + (1 x 20).
May I do this calculation with just 1 SUMPRODUCT without supporting column?
Need your help, thanks alot.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Richard Buttrey]

...in addition since this is clearly a much simplified example and probably doesn't represent the size or layout of your actual data, we often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[navafolk]

...in addition since this is clearly a much simplified example and probably doesn't represent the size or layout of your actual data, we often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

Thank you Mods Richard Buttrey and AliGW,
I uploaded my sample excel file with some manual calculations and expected result. Hope for your helps.

[Richard Buttrey]

I can't see any easy way using SumProduct just at the moment

Why the reluctance to add a helper column the sum of which would give your anwer?

[Bo_Ry]

Please try
=SUM((IF(Table2[date2]>B2+1,B2+1,Table2[date2])-Table2[date1])*Table2[amount])
Confirm by Ctrl+Shift+Enter

[samba_ravi]

if you want to use sumproduct try below
=SUMPRODUCT((((Table2[date2]<=Table1[Value]+1)*Table2[date2]+(Table2[date2]>Table1[Value]+1)*(Table1[Value]+1))-Table2[date1])*(Table2[amount]))

[navafolk]

if you want to use sumproduct try below
=SUMPRODUCT((((Table2[date2]<=Table1[Value]+1)*Table2[date2]+(Table2[date2]>Table1[Value]+1)*(Table1[Value]+1))-Table2[date1])*(Table2[amount]))

Great, thanks Samba.
Really smarter, prettier and shorter than what I though yesterday.
=SUMPRODUCT(--(Table2[date2]<(Table1[Value]+1)),Table2[date2]-Table2[date1],Table2[amount])
+SUMPRODUCT(--(Table2[date2]>=(Table1[Value]+1)),Table2[date2]-Table2[date1],Table2[amount])