Add number if there are duplicates

Airtable formulas generally aren't "aware" of records other than the one it's currently operating on – and then, because formulas have no access to "record order", you can't really have it assign a number that increments for each additional matching record.

If the autonumber doesn't suit your purposes and you want sequential numbering of duplicates, you would need a script, either one that runs on demand or via automation.

You can do it manually. Example

add autonumber. add column right to your field where you search duplicates, type "link to a table (select new)"

copy-paste your field there.
Now go to New Table and create such rollup

space after semicolon is mandatory
now return to your table and throw this rollup via lookup here (i renamed it to just 'rollup' to soften the formula)

and now craft formula that finds 'index' of current element ID in the whole list of similar elements, then counts elements of the left side of it (by substituting semicolon, for example)

I hope you understand how it works and what to do next ))
It might look hard and crazy, but indeed it's not, when you do it several times.
In Airtable, record 'doesn't know about each other', so to operate totals and other summaries, you need to create a pivot table (method with link by copy-pasting) and perform rollup/lookup/count operations between these two tables.