Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community

Count First Occurence Only

Copper Contributor

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.

5 Replies

@Tolley123 

=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.

extended price update.png

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.

@Tolley123 

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)))

image.png

@Tolley123 

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

@Tolley123 

DaeyunPablo_0-1704213893548.png

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

I believe COUNTA should work since Excel 2003.