Eric...thanks for the support - the last formula worked perfectly.
I would like to add one more variable to the equation (just came up)...If the formula is carried across several years, I want to be able to choose between Cumulative or Annual Reset. In the Annual Reset option, the volume tiers would reset in January of each year. An example of this is in the following data...please note that I am using cell B2 to make the selection between options of "Annual" or "Cumulative". I know that the formula already provided works for "Cumulative" but what about an annual reset?
Annual Example
[TABLE="width: 1573"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]D[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Model[/TD]
[TD]Annual[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="colspan: 2"]Monthly Volume[/TD]
[TD][/TD]
[TD]1,000[/TD]
[TD]2,500[/TD]
[TD]3,005[/TD]
[TD]4,000[/TD]
[TD]5,000[/TD]
[TD]6,000[/TD]
[TD]7,000[/TD]
[TD]8,000[/TD]
[TD]9,000[/TD]
[TD]10,000[/TD]
[TD]11,000[/TD]
[TD]12,000[/TD]
[TD]11,000[/TD]
[TD]12,000[/TD]
[TD]13,000[/TD]
[TD]14,000[/TD]
[TD]15,000[/TD]
[TD]16,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="colspan: 2"]Cumulative Volume[/TD]
[TD][/TD]
[TD]1,000[/TD]
[TD]3,500[/TD]
[TD]6,505[/TD]
[TD]10,505[/TD]
[TD]15,505[/TD]
[TD]21,505[/TD]
[TD]28,505[/TD]
[TD]36,505[/TD]
[TD]45,505[/TD]
[TD]55,505[/TD]
[TD]66,505[/TD]
[TD]78,505[/TD]
[TD]89,505[/TD]
[TD]101,505[/TD]
[TD]114,505[/TD]
[TD]128,505[/TD]
[TD]143,505[/TD]
[TD]159,505[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="colspan: 2"]Tier[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]1,000[/TD]
[TD]2,500[/TD]
[TD]1,500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5001[/TD]
[TD]13000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1,505[/TD]
[TD]4,000[/TD]
[TD]2,495[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6,000[/TD]
[TD]6,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]13001[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2,505[/TD]
[TD]6,000[/TD]
[TD]3,495[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]25001[/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3,505[/TD]
[TD]8,000[/TD]
[TD]9,000[/TD]
[TD]4,495[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13,000[/TD]
[TD]12,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]50000[/TD]
[TD]1000000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5,505[/TD]
[TD]11,000[/TD]
[TD]12,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]15,000[/TD]
[TD]16,000[/TD]
[/TR]
</tbody>[/TABLE]
Thanks,
Chris