# Countif (display) first instance of a number only

#### ndube50

##### New Member
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?

Nick.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Copy D18 across (I'm assuming C17 is blank or less than 500)

Excel Workbook
DEFGHIJKLMNOPQRSTUV
162011201220132014201520162017201820192020202120222023202420252026202720282029
17501333050708090180200300400
1830
Sheet4

Last edited:
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

Thanks so much. This worked great!

Thanks Robert. This also worked.
Much appreciated!

L
Replies
10
Views
1K
Replies
11
Views
1K

1,203,521
Messages
6,055,885
Members
444,830
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back