Hello

I have the following sumproduct formula in row M2 in a sheet

=-SUMPRODUCT((L11:L372)*(M11:M372))

Cells in the specified range above in in Column L have a formula that returns either a 0, 1 or -1.

I want the start range for the formula to begin on the first occurance of a 1 in column L and the end range to end at the last occurance of -1 in column L. Is it possible to get the sumproduct to autoselect the range based on this criteria?

For example, if the first 1 only occurs until L15, and the last -1 occurs in L360, then I only want to run the SUMPRODUCT formula on the range ((L15:L360)*(M15:M360)).

A couple more examples should make it clear

First 1 occurance: L20
Last -1 occurance L300
Range: Pick up only cells L20:L300 & M20:M300 in formula

First 1 occurance: L91
Last -1 occurance L358
Range: Pick up only cells L91:L358 & M91:M358 in formula

Thanks,

Hamond