Sum/Count based on sequence

Trying to find a formula that would identify if a unique id hits a particular threshold and if so, I would like it to pull the dollar value from the row in which the threshold is met.
I need the formula to cumulatively add the amount(column D) and number of line items. If the unique identifier (column A) >= count of 4 and the sum of amount (column D) is >= $400 I would like the formula to pull only the amount where both criteria are met.

For example in the attached file the unique identifier PAYNE PAYNE-57029-NM has 6 line items and would meet the criteria on the 4th line, therefor the result I'd be looking for would $277.96, ignoring the other amounts after it.

Here is one solution using helper columns.

The *** Columns take advantage of relative cell addressing to get a count or sum of all cells following it. This gets subtracted from the total sum.
*** Count =COUNTIF($A$2:$A$20,A2) - COUNTIF($A2:A20,A2) +1
*** Amount =SUMIFS($D$2:$D$20,$A$2:$A$20,A2)-SUMIFS(D2:D$20,A2:A$20,A2)+D2

Pull sees if an individual line meets or exceeds the criteria.
Pull =AND(F2>=4,G2>=400)

Match finds the fist occurrence of True for the Link. =MATCH(A2&H2,$A$1:$A$20&$H$1:$H$20,0) <- Array Formual

Flag shows the cells that are wanted: those that meet or exceed the requirement and are the first occurrence =AND(H2=TRUE,I2=ROW())

Thank, this was very helpful. There are also scenarios where the dollar value criteria is not met until after the count of 4. The current formula provided will not display results in those scenarios? Any thoughts?

Thanks again
Ulises

Yes it worked great, my file is around a million rows of data so I was trying to reduce the number of formulas. thanks again for the help.

These formulas worked great. How does the array formula work?

If you mean how does one make a formula an array formula: hold down CTRL-SHIFT and then ENTER. If you mean what does the formula do, then this wiki explains the concept: http://www.utteraccess.com/wiki/inde...Array_Formulas.