Sum Calculation using Reference values from other columns

[supernova2008]

A	B	C	D
23	35	2.3	4567
32	23	0.5	658
45	76	-1.7	2585
76	45	2.5	2574
45	27	-3.4	1573
34	82	1.2	4793
95	41	-3.8	2634
31	55	-6.1	6391
56	58	3.9	2772

<tbody>
</tbody>

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.