Here is a dynamic formula that you add one time, and it will always calculate based on column C:

= LET(c, DROP(C:.C,1), MAP(c, SEQUENCE(ROWS(c)), LAMBDA(m,i, COUNTIFS(TAKE(c,i),m))))

Here is the same formula wrapped in LAMBDA that you can store in your name manager and call as custom function:

= LAMBDA(category_column, LET(c, DROP(TRIMRANGE(category_column,2),1), MAP(c, SEQUENCE(ROWS(c)), LAMBDA(m,i, COUNTIFS(TAKE(c,i),m)))))(C:C)