Challenge: creating Herfindahl index for patent concentration

Dear people,

For my master thesis I need to create a patent concentration ratio. The idea is to get a number between 0-1, indicating how large of a part of a firm's patents are from one specific industry (segment). I have the patent classification numbers for 110 firms ranging from 5 to 100 patents. To create the index number, I would need to sum the squares of the shares of patents. Example:

A firm with 5 patents (e.g. A61K3, C02F1, C12N9, C12N9, C12N9) would have an index of ((1/5)^2))+((1/5)^2))+((3/5^2)) = 0.44

I have three colums for each firm, one with 3 digits (e.g. A61), one with 4 digits (e.g. A61K), and one with 5 digits (e.g. A61K3). Ideally I would want the Herfindahl index for all three columns for all firms.

Doing it as I tried (see attachment, numbers in red) would take too much time. If anyone would be willing to take a look, and could suggest a way to make the process easier, that would be extremely appreciated! I would even credit you in my thesis .

Cheers!

Fabian