Sum Calculation using Reference values from other columns

[supernova2008]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]2.3[/TD]
[TD="align: center"]4567
[/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]658[/TD]
[/TR]
[TR]
[TD="align: center"]45[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]-1.7[/TD]
[TD="align: center"]2585[/TD]
[/TR]
[TR]
[TD="align: center"]76[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]2574[/TD]
[/TR]
[TR]
[TD="align: center"]45[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]-3.4[/TD]
[TD="align: center"]1573[/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]1.2[/TD]
[TD="align: center"]4793[/TD]
[/TR]
[TR]
[TD="align: center"]95[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]-3.8[/TD]
[TD="align: center"]2634[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]-6.1[/TD]
[TD="align: center"]6391[/TD]
[/TR]
[TR]
[TD="align: center"]56[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]3.9[/TD]
[TD="align: center"]2772[/TD]
[/TR]
</tbody>[/TABLE]

Need to calculate sum of nos. provided in column D (Range for calculation in column D should be from row no. of maximum in column A to row no. of minimum in column B and for corresponding negative values of column C.

please help

 

[supernova2008]

in this case :
Maximum value in column A = 95
Row no. of maximum value = 8
Minimum value in column B = 23
Row No. of minimum value = 3

So the sum with corresponding negative value between between Row no. 3 to Row no. 8 should be 2585+1573+2634 = 6792

 

[Tetra201]

The following formula returns the expected result for the posted dataset:

=SUMPRODUCT(--(ROW(D1:D10)-ROW(D1)+1<=MAX(MATCH(MAX(A1:A10),A1:A10,0),MATCH(MIN(B1:B10),B1:B10,0))),--(ROW(D1:D10)-ROW(D1)+1>=MIN(MATCH(MAX(A1:A10),A1:A10,0),MATCH(MIN(B1:B10),B1:B10,0))),--(C1:C10<0),D1:D10)

 

[supernova2008]

The following formula returns the expected result for the posted dataset:

=SUMPRODUCT(--(ROW(D1:D10)-ROW(D1)+1<=MAX(MATCH(MAX(A1:A10),A1:A10,0),MATCH(MIN(B1:B10),B1:B10,0))),--(ROW(D1:D10)-ROW(D1)+1>=MIN(MATCH(MAX(A1:A10),A1:A10,0),MATCH(MIN(B1:B10),B1:B10,0))),--(C1:C10<0),D1:D10)

Range is Dynamic......

 

[Tetra201]

Range is Dynamic......

Yes, this is correct.