Formula to subtract from balance based on ranking

[DerekWooley]

Hello, I have a list of part numbers that I want to subtractthe total quantity of based on a ranked used date. In my below table, I amusing a rank formula
=1+SUMPRODUCT(($A$3:$A$7=A3)*($G$3:$G$7<G3))
But I don’t know how to subtract each used quantity inColumn D from the total in column C based on the ranked date from smallest tolargest in Column H. For example, I would start with 7266 and subtract 60 firstsince that is the highest ranked, making part # 20321 have the value 7206. Thesecond ranked would subtract 2880 from the 7206 balance since it is ranked 2.This would continue until I hit the highest ranked number. I can’t sort thetable and many of my parts for other materials have ranking that vary from 1 to9.

I am also using excel 2003.



[TABLE="width: 390"]
<tbody>[TR]
[TD="width: 69, bgcolor: transparent"]

Column A​

[/TD]
[TD="width: 64, bgcolor: transparent"]

Column B​

[/TD]
[TD="width: 67, bgcolor: transparent"]

Column C​

[/TD]
[TD="width: 63, bgcolor: transparent"]

Column D​

[/TD]
[TD="width: 65, bgcolor: transparent"]

Column F​

[/TD]
[TD="width: 77, bgcolor: transparent"]

Column G​

[/TD]
[TD="width: 115, bgcolor: transparent"]

Column H​

[/TD]
[/TR]
[TR]
[TD="width: 69"]

Material ID​

[/TD]
[TD="width: 64"]

Part #​

[/TD]
[TD="width: 67"]

Qty Rec'd​

[/TD]
[TD="width: 63"]

Qty Used​

[/TD]
[TD="width: 65"]

Rank​

[/TD]
[TD="width: 77"]

Dates Used​

[/TD]
[TD="width: 115"]

Needed Formula​

[/TD]
[/TR]
[TR]
[TD="width: 69, bgcolor: transparent"]

7302899​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2342​

[/TD]
[TD="width: 67, bgcolor: transparent"]

7266​

[/TD]
[TD="width: 63, bgcolor: transparent"]

2064​

[/TD]
[TD="width: 65, bgcolor: transparent"]

5​

[/TD]
[TD="width: 77, bgcolor: transparent"]

6/1/2018​

[/TD]
[TD="width: 115"]

1194​

[/TD]
[/TR]
[TR]
[TD="width: 69, bgcolor: transparent"]

7302899​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2037​

[/TD]
[TD="width: 67, bgcolor: transparent"]

7266​

[/TD]
[TD="width: 63, bgcolor: transparent"]

2880​

[/TD]
[TD="width: 65, bgcolor: transparent"]

2​

[/TD]
[TD="width: 77, bgcolor: transparent"]

3/28/2018​

[/TD]
[TD="width: 115"]

4326​

[/TD]
[/TR]
[TR]
[TD="width: 69, bgcolor: transparent"]

7302899​

[/TD]
[TD="width: 64, bgcolor: transparent"]

20321​

[/TD]
[TD="width: 67, bgcolor: transparent"]

7266​

[/TD]
[TD="width: 63, bgcolor: transparent"]

60​

[/TD]
[TD="width: 65, bgcolor: transparent"]

1​

[/TD]
[TD="width: 77, bgcolor: transparent"]

2/8/2018​

[/TD]
[TD="width: 115"]

7206​

[/TD]
[/TR]
[TR]
[TD="width: 69, bgcolor: transparent"]

7302899​

[/TD]
[TD="width: 64, bgcolor: transparent"]

3029​

[/TD]
[TD="width: 67, bgcolor: transparent"]

7266​

[/TD]
[TD="width: 63, bgcolor: transparent"]

1020​

[/TD]
[TD="width: 65, bgcolor: transparent"]

4​

[/TD]
[TD="width: 77, bgcolor: transparent"]

5/25/2018​

[/TD]
[TD="width: 115"]

3258​

[/TD]
[/TR]
[TR]
[TD="width: 69, bgcolor: transparent"]

7302899​

[/TD]
[TD="width: 64, bgcolor: transparent"]

4129​

[/TD]
[TD="width: 67, bgcolor: transparent"]

7266​

[/TD]
[TD="width: 63, bgcolor: transparent"]

48​

[/TD]
[TD="width: 65, bgcolor: transparent"]

3​

[/TD]
[TD="width: 77, bgcolor: transparent"]

5/24/2018​

[/TD]
[TD="width: 115"]

4278​

[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help!

 

[StephenCrump]

H3: =C3-SUMPRODUCT(--(A$3:A$7=A3),--(G$3:G$7<=G3),D$3:D$7)

(and then you don't need the column F ranks).

If duplicate dates are a possibility (?) you might want to check that the formula works the way you want.

 

[DerekWooley]

This works perfectly. Thank you so much.