Thread: Is it possible to have an IF inside SUMPRODUCT?

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

Try confirming the formula with Ctrl+Shift+Enter.

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.

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:

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

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)

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.