Conditional Subtotal formula

[sandmn321]

Hi I am trying to make a calculation and have attached a sample workbook for reference. Book1(2).xlsx


In the recovery column, I need a dollar value. If the "6 Month Occurrences for Part Number" is greater than or equal to 3, then the recovery cost is computed Qty*Cost*25% OR $1000 whichever is less. If the "6 month Occurrences for Part Number" is less than 3, then the recovery cost is $0.

I would prefer if the recovery cost for for each Part number is subtotalled.
The recovery costs are calculated separately for each part number.

Any help would be greatly appreciated.

[rvasquez]

Hello there,

Try the following formula in row 2:

=IF(H2>=3,IF(D2*E2*0.25>1000,1000,D2*E2*H2),0)

[sandmn321]

rvasquez,

That solution didn't quite yield the results that I desired. I have manually calculated a portion on the sample workbook and it is attached here.Book1(2)_Manual.xlsx

If column J is marked "Recover", then I need to calculate the cost as described in my previous post for all rows where column G <= column C in column K. In column L, I need to subtotal the Recovery cost in column K for that part number (column B). In column M, the value is =IF(L19>1000,1000,L19).

[rvasquez]

I'm a little confused, your original post states:

In the recovery column, I need a dollar value. If the "6 Month Occurrences for Part Number" is greater than or equal to 3, then the recovery cost is computed Qty*Cost*25% OR $1000 whichever is less. If the "6 month Occurrences for Part Number" is less than 3, then the recovery cost is $0.

If these conditions are met then cell K8 would not be 21836.86 it would be 1000. Could you please let me know what you would like? Based on what you manually inputted it seems as though you just want it so that if Column J is Recover then calculate Qty * Cost * .25 in column K

[sandmn321]

I will try to clarify. I manually computed the recovery cost for each Tag marked "Recover" as well as tags where the date in column C occured after the date in column G using the formula Qty*Cost*.25.

From the recovery cost column, I totalled the recovery cost for a single part number over this period. For example, Recover period 1 is from April 18, 2004 (G8) to April 26, 2005 (C19). Since C6>=G8, row 6 is the start point for that recovery period.

Summing J6-J19 the recovery cost for this period is 45,067.56 which is greater than 1,000.00 so the amount billed in M19 of 1,000.00 is for the Tags in Row8-Row19.

The solution doesn't need to be expanded over columns K-M like my manual solution is, I was just trying to show the process of the calculation.

I hope that this is more clear.

[sandmn321]

I have come up with a solution on my own. Thank you for your efforts.

[rvasquez]

No problem, sorry I couldn't get around to helping had my plate full at work. Glad you could get something to work for you!