Sumproduct (with vlookup)

[tmontana]

I have a data table with monthly amounts starting in Jan-2011 to Dec-2050 (480 months/columns) from columns C to RO. In column A, I have the property number and in column B I have the definition of the cashflow item (i.e. NOI, Rent, Expenses, etc.). There are 29 properties and multiple cashflow items.

I would like to sum the monthly amounts based on a start and end period for a specific property number and cashflow item.

The formula:

=SUMPRODUCT(--(Sheet2!$A$1:$A$78="IPP1"),--(Sheet2!$B$1:$B$78="NOI"),--(Sheet2!$D$1:$RO$1>=AM$1),--(Sheet2!$D$1:$RO$1<=AM$2),Sheet2!$A$1:$RO$78)

doesn't seem to work. I know with sumproduct the arrays need to be the same and in this case they are not. Should i be combining this with vlookup and if so how?

Thanks

 

[Matty]

Does this yield the expected result:

=SUM(IF(Sheet2!$A$2:$A$78="IPP1",IF(Sheet2!$B$2:$B$78="NOI",IF(Sheet2!$D$1:$RO$1>=AM$1,IF(Sheet2!$D$1:$RO$1<=AM$2,Sheet2!$D$2:$RO$78)))))

Which is an array formula and therefore needs committing with CTRL+SHIFT+ENTER.

Matty

 

[tmontana]

It doesn't seem to work. I'm getting NA. The formula picks out the correct row, but it is not looking up the correct dates.

 

[tmontana]

My bad. It does work. THanks Matty.