Rows are making errors in formula

[momofhim]

Hello, I have the following formula: SUMPRODUCT((YEAR(Breakdown1!$N$2:$N$155)<YEAR('Capacity v Demand'!C$1))*(Breakdown1!$O$2:$O$155='Capacity v Demand'!$A2)*(Breakdown1!$P$2:$P$155))

On the Breakdown sheet the number of rows is constantly changing. With the number of rows constantly changing I am getting #VALUE errors. in the capacity v demand work sheet. I'm not sure how to change my formula to accommodate for the changing number of rows.

Thanks in advance

[Pecoflyer]

Hi
the easiest way is to use Excel Tables

[RET]

You could also use INDIRECT to build the target range. Count the number of rows with data and concatenate the target address into INDIRECT