SumProduct Excel 2010

[fasullo]

Hi,
I have a complex sum problem. Excel located here: https://dl.dropboxusercontent.com/u/52990313/sum product.xlsx

Column A is the # of months
Row 1 are monthly dates starting in B1 from 9/30/12 on...
from B2:BA9 are the numbers to sum

Here is the goal of the formula:
1) Has to start adding only when a positive number is in a cell. Otherwise, you start adding #s on 11/30/12 (D2) greater than 12 mo, but the contract did not start until 12/31/12 (E2)

2) Only want to look at months > 12 in column A

3) Want it to look across row 1 to start a sum for 12+ months from current month
Thinking for Dec 2012 ? (E$1:BA1>DATE(YEAR(E$1)+1,MONTH(E$1),DAY(E$1)))

really appreciate it !

 

[markmzz]

Try this:

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

E11-> =SUM(IF($A$2:$A$9>12,IF($B$1:$BA$1>=EDATE(E$1,12),(E$2:E$9>0)*$B$2:$BA$9)))

Markmzz

 

[fasullo]

as always, thanks! works great.
I'm curious, is there anyway to do it w/0 an array ?

 

[markmzz]

as always, thanks! works great.
I'm curious, is there anyway to do it w/0 an array ?

Try this:

=SUMPRODUCT(($A$2:$A$9>12)*($B$1:$BA$1>=EDATE(E$1,12)),(E$2:E$9>0)*$B$2:$BA$9)

Markmzz

 

[fasullo]

this is fantastic! thanks

 

[markmzz]

this is fantastic! thanks

You're welcome.

And I'm glad to help.

Markmzz