Creating a count within groups of rows

One other note: It is a little scary relying on formulas for these SKU numbers. You should keep good backups in case something goes amiss.

You could, from time to time, select all the SKU numbers that have been made to date (or just those still using the formula), Copy, then Paste Formula Results back to the same cells to "lock them in". Then, even if something happens, at least those ones are locked in.

If doing it manually, you can add a column to check for duplicates, which might happen when doing it manually.

If the SKUs are in column B,

=IF(COUNTIF(B,B)>1,"DUPLICATED", "")

This will flag any duplicates for you to correct. It flags the original and any/all duplicates.

If you use the formula method to create the SKU, you can include a column that tells you to lock in the results. This might be easier than typing in a SKU; you are given the SKU and all you have to do is lock it in. Create a simple keyboard shortcut for Paste Formula Results and all it will take is Cmd C (copy) followed by your new shortcut. This does require, though, that you not change any SKUs afterward or manually enter any that are out of order. That would break the formula that comes up with the next SKU.

G2 =IFERROR(IF(AND(C2, FORMULATEXT(F2)<>""),"Remember to Lock in the SKU!", ""), "")

Just some thoughts. It does get complicated. It may be better to manually enter them and use the formula to flag duplicates.

This can be done. There is more than one set of formulas that will do it. One is presented below. Simpler formulas may be posted by others later, more than likely.

Formulas are often simpler and easier to debug if the problem is split into parts vs trying to do too much in one cell. For that reason, I added a few columns to your table, all of which can be hidden later. Note that, with formulas vs manually entered SKU numbers, you have to be aware of what sorting might do to your SKU numbers. You don't want everything renumbering itself as a result of the sort. I designed the formulas below so that the table could be sorted.

Column A, the Index Number column, is actual numbers, not formulas. I added this to make the table sortable without all your SKU numbers changing. You will see it in the formula for the "design number". You can keep adding numbers at the bottom but always keep increasing it, never skip a row and fill it in later, and never renumber a row that already has a product and a SKU number.

Column C just checks if there is a valid product name in column B. Only words that begin with A-Z (upper case only) are allowed. I used the ASCII codes for this check. This result is used in the other formulas. If the name is valid, the other formulas will come up with the SKU, otherwise they will not.

C=IFERROR(AND(CODE(B)>64, CODE(B)<91),FALSE)

Column D, the design code, turns the first letter of the product name into a number. It appends a "0" to the front (for single digit results) but then truncates the result to the 2 rightmost characters (for two-digit results).

D =IF(C,RIGHT("0"&(CODE(B)−64),2),"")

Column E, Design Number, uses the index in column A to determine which design number this product will get.

It counts how many rows have the same Design Code (column D) and a lower index number (column A). Actually the second check is "less than or equal to" and it counts the current row so the answer will be at least 1.

E =IF(C,RIGHT("00"&COUNTIFS(D,D,A,"<="&A),3),"")

Column F puts it all together

F =IF(C,"SKU"&D&E,"")