Using PRODUCT function within SUMIF

[AndreK123]

Hi,

I want to use the PRODUCT function (to compound % returns data) within a SUMIF statement as looking for the formula to only compound numbers between 2 dates

Can anyone help??

Thank you

 

[Harold Garcia]

Hi,

I want to use the PRODUCT function (to compound % returns data) within a SUMIF statement as looking for the formula to only compound numbers between 2 dates

Can anyone help??

Thank you

Do you have a sample table with data?

 

[AndreK123]

Feb 16	Mar 16	Apr 16	May 16	Jun 16	Jul 16	Aug 16	Sep 16	Oct 16
-0.01%	0.34%	1.25%	-0.02%	0.60%	1.38%	2.38%	2.35%	0.58%

<colgroup><col span="9"></colgroup><tbody>
</tbody>

So I want to create a formula that only compounds the numbers between Mar 16 and Sep 16

But I want it to be flexible so that I can change the start and the end date that it includes

 

[Aladin Akyurek]

What is the expected result?

 

[AndreK123]

8.55% compounded

 

[AndreK123]

=SUMIF($O$12:$AX$12,"<="&Portfolio!$I$766,'Mth Pos PnL'!O175:AX175)-SUMIF($O$12:$AX$12,"<="&EOMONTH(Portfolio!$I$766,-12),'Mth Pos PnL'!O175:AX175)
That is my current formula
All is says is that sum returns in columns O to AX (where the monthly returns are) if they are between two dates
Instead of a sum formula (which is not compounding the monhtly returns) - I want to use a product formula so that it does do the compounding

 

[pgc01]

Hi

See if this example works.

With

- the data you posted in A1:I2
- start and end dates in M2:N2
- try in O2:

=SUMPRODUCT(PRODUCT(1+(A1:I1>=M2)*(A1:I1<=N2)*A2:I2))-1

 

[AndreK123]

Thank you will try, but is there a formula that can make me able to copy down so calculate compound returns for multiple dataseries while keeping the dates on top? As I have several rows with different return data that all need to be calculated

 

[pgc01]

Hi

Not clear about your setup

Anyway you just have to freeze the addresses that do not change.

For ex., with the dates on top like in the example and the start and end dates always M2:N2, use in J2:

=SUMPRODUCT(PRODUCT(1+($A$1:$I$1>=$M$2)*($A$1:$I$1<=$N$2)*A2:I2))-1

copy down