Hamond
06-30-2009, 04:50 AM
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
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