I tried this and D2 to D5 all equal 14. This is correct for D2, but then D3 to D5 are also referencing ($A$2:$A$5=A2) as they all have the same formula. If I try to edit the formula or insert a row or column, the following error displays: You can't change part of an array.
Conditional formatting is being used depending on the text so I can't just use numbers. All matching text need to be linked to a number, and I need the total sum based on the name in Column A as they are listed multiple times.