Count First Occurence Only

Question

Help requested please.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.Thanks in advance.

oliverscheurich · Answer

Tolley123&nbsp;=IF(SMALL(IF(NOT(ISBLANK($E$2:$E$9)),ROW($E$2:$E$9)),1)=ROW(),(B2*E2)+D2,D2)&nbsp;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.

tolley123 · Answer

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.

sergeibaklan · Answer

Tolley123&nbsp;
That is strange Excel 2007 definitely works with mentioned functions&nbsp;Excel functions (by category) - Microsoft Support&nbsp;if the speak about English function names.
&nbsp;
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)))

hansvogelaar · Answer

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

daeyunpablo · Answer

Tolley123&nbsp;=IF(COUNTA($E$2:E2)=1,D2+B2*E2,D2)I believe COUNTA should work since Excel 2003.

Forum Discussion

Count First Occurence Only

Resources