Countif (display) first instance of a number only

[ndube50]

I am creating a financial model where I am trying to calculate the NPV and rNPV of several assets.

One of the potential streams of revenue is a one-time 'milestone' payment of $30M if/when the cumulative sales of the product exceeds $500M.

I have projected revenues from product sales for years 2012-2032 in row D17:Y17.Cumulative sales exceed $500M in the year 2019 (column L).

I want to have a line item (row) where the $30M milestone will register automatically in the proper year (in this case 2019). The problem I have is that the $30M payment will show up in subsequent years as well (i.e. 2020, 2021, etc).

How can I tell excel to only report the one-time milestone payment?

thank you for your help.

Nick.

 

[sheetspread]

Copy D18 across (I'm assuming C17 is blank or less than 500)

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
16	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029
17	501	3	3	30	50	70	80	90	180	200	300	400
18	30
Sheet4

 

[Trebor76]

Hi Nick,

Assuming Row 18 is to display the result, ensure cell C18 is blank copy this formula into cell D18 and copy it across to cell Y18:

=IF(AND(SUM($D$17:D17)>500,SUM($C$18:C18)=0),30,0)

HTH

Robert

 

[ndube50]

Thanks so much. This worked great!

 

[ndube50]

Thanks Robert. This also worked.
Much appreciated!