Is it possible to have an IF inside SUMPRODUCT?

[skysurfer]

Hi,

Is it possible to use an IF function within Sumproduct?

I am trying to create a formula that looks like this:

Sumproduct((RangeA)*if(A1=TRUE,RangeB,RangeC)*(RangeD))

The actual formula looks like this:

=SUMPRODUCT((Chart5DateAll<=EOMONTH(F$132,0))*IF(Chart5PeriodFiscalYTD=TRUE,OFFSET(Chart5DateAll,1,0)=OFFSET($F$104,0,MATCH(F$132,Chart5DateAll,0)-1),Chart5DateAll>EOMONTH(F$132,-Chart5PeriodIncrement))*OFFSET(Chart5DateAll,ROWS(F$103:F110)-1,0))

Problem is it works without the IF function.

Thanks,

Lawrence

[shg]

Try confirming the formula with Ctrl+Shift+Enter.

[skysurfer]

Try confirming the formula with Ctrl+Shift+Enter.

It works, but I do not understand why.

Is there as way to make it work without turning it into an array? Concerned about slowing down the model as there are hundreds of these calcs.

Thanks for the help!!!

Lawrence

[shg]

It was already an array formula to all intents and purposes. Your time would be better spent making it non-volatile -- replace OFFSET with INDEX, for example -- and avoid duplicatively calculating things that could be done once in a helper cell, like the EOMONTH funtion, possibly.

[DonkeyOte]

I agree with all of shg's points (obviously).

I would say that if

a) you need to keep using a SUMPRODUCT (ie you're not running XL2007 [COUNTIFS] and don't want to use 2 COUNTIFs)

and/or

b) you want to avoid CTRL + SHIFT + ENTER entry

then split the formula via a pre-emptive IF, eg:

Please Login or Register  to view this content.

The above removes the need for CTRL + SHIFT + ENTER and the SUMPRODUCT is still calculated only once however the formula itself is obviously longer.

Whichever route you take - as shg has already pointed out - the Volatility will generate significant overheads.
The Volatility means the above will recalculate with every Volatile action undertaken in your model irrespective of whether or not said action has a direct impact on the precedents of your function.

If you want help doing this I would suggest posting a sample file.
We can see you're adjusting ranges for use with the SUMPRODUCT but from the formula alone it's hard to establish possible non-volatile (and indeed non-array/Sumproduct) alternatives.

(SUMPRODUCTs & Arrays are "processed" in the same way - a SUMPRODUCT is not really noticeably quicker than an Array (if at all) - it's a debated topic but most agree that if a difference does exist - it's small)

[DonkeyOte]

Per the note at MrExcel - if you choose to x-post please have the courtesy to provide links accordingly

http://www.mrexcel.com/forum/showthread.php?t=452969

This applies to all forums.

[skysurfer]

It was already an array formula to all intents and purposes. Your time would be better spent making it non-volatile -- replace OFFSET with INDEX, for example -- and avoid duplicatively calculating things that could be done once in a helper cell, like the EOMONTH funtion, possibly.

Thanks for your feedback. I've used it to resolve the issue and improve the model!

Lawrence