# Count First Occurence Only

Copper Contributor

# Count First Occurence Only

Refer to the sample data set provided.
The requirement is to have a formula copied down in column F, that provides an Extended Price Update for only the row that has the first occurrence of an Additional Qty. value in column E (per the example data set, row 4). All other column F results (subsequent to the first occurence in row 4 are required to ignore any column E Additional Qty's. The results in column F are expected to show 5,600 in row 4, and in all other rows the figures shown in column D.

5 Replies

# Re: Count First Occurence Only

=IF(SMALL(IF(NOT(ISBLANK(\$E\$2:\$E\$9)),ROW(\$E\$2:\$E\$9)),1)=ROW(),(B2*E2)+D2,D2)

You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

# Re: Count First Occurence Only

Hi _____, I appreciate your response, however I using an older version of Excel (2007) which does not support the NOT and ISBLANK functions.
Any further suggested solution utilising version 2007 functionality would be very much appreciated.
Thanks.

# Re: Count First Occurence Only

That is strange Excel 2007 definitely works with mentioned functions Excel functions (by category) - Microsoft Support if the speak about English function names.

Please try to open attached file in your environment. Idea of the formula is the same, just changed a bit to avoid array formulae.

``=B2*(C2+E2*(ROW() = AGGREGATE(15,6,\$E\$2:\$E\$9/\$E\$2:\$E\$9*ROW(\$E\$2:\$E\$9),1)))``

# Re: Count First Occurence Only

NOT and ISBLANK are supported in all versions of Excel, at least in the English versions. Do you use a different language?

# Re: Count First Occurence Only

``=IF(COUNTA(\$E\$2:E2)=1,D2+B2*E2,D2)``

I believe COUNTA should work since Excel 2003.